Consider that you have a table as below and you want to select some five rows from each column. In such situation you can use join statement.
ID Dept Names
---------------------
1 ECE Mark
2 ECE Kris
3 ECE Chris
4 EEE Ben
5 EEE Chris
6 CSE Marc
7 CSE Math
8 CSE Sara
SELECT t1.id,t1.Names,t1.Dept,COUNT(t2.id) AS cnt
FROM COLLEGE AS t1
LEFT JOIN COLLEGE AS t2
ON (t1.Names, t1.id) <= (t2.Names, t2.id)
AND t1.Dept = t2.Dept
GROUP BY t1.id,t1.Names,t1.Dept
HAVING cnt <= 4 ORDER BY t1.Dept, cnt
Output:
ECE EEE CSE
------- ------ --------
Mark Ben Marc
Kris Chris Math
Chris Sara
You can modify this query and can get first or last five rows from your database. This query takes little time to execute than other queries, but it will reduce the execution of multiple quries. I feel that it is better in performance insted of executing multiple queries.
|
