Mysql - Query to get 5 rows from each category, Select first least max row per group in sql

Query to get 5 rows from each category

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.

The topic on Mysql - Query to get 5 rows from each category is posted by - Math

Hope you have enjoyed, Mysql - Query to get 5 rows from each categoryThanks for your time