Select different number of records by group

Question

I have a database in which each record has a grade and is associated to a certain group.
I need to select the best grades in each group, not the usual top 5, but a different number in each group.

In the following example, there are 10 records and two groups- A and B.

I want the highest 2 grades in group A and the highest 3 in group B:

ID  Group   Grade
1     A      4
2     B      2
3     B      4
4     A      7
5     A      8
6     A      5
7     B     10
8     B      3
9     B      9
10    A      7

The query should select records 4 and 5 of group A and records 3, 7 and 9 in group B.

I will appreciate any ideas to make this query.

Regards

I would caution against using reserved words as table/column identifiers. — Strawberry, Mar 07 '19 at 16:23
I would simply do `UNION` of selects in single groups. Like `SELECT UNION SELECT UNION ...` — Roman Hocke, Mar 07 '19 at 16:23
@RomanHocke You'd have to make them in to subqueries for that to work. — Strawberry, Mar 07 '19 at 16:24
also `TOP` is SQL Server (MSSQL) @RomanHocke MySQL uses `LIMIT` — Raymond Nijland, Mar 07 '19 at 16:37
Unimportant details. The point is - I would select result for groups A and B separately and then union them into single result (if this is desired form of result). — Roman Hocke, Mar 07 '19 at 16:47

LukStorms · Answer 1 · 2019-03-07T17:01:47.050

0

Only 2 groups, but with a different top N?

Then I guess a union with 2 limits could do the job in a MySql 5.* version.

SELECT ID, `Group`, Grade
FROM 
(
   SELECT * FROM YourTable 
   WHERE `Group` = 'A' 
   ORDER BY Grade DESC, ID ASC
   LIMIT 2
) A
UNION ALL
SELECT ID, `Group`, Grade
FROM 
(
   SELECT * FROM YourTable 
   WHERE `Group` = 'B' 
   ORDER BY Grade DESC, ID ASC
   LIMIT 3
) B
ORDER BY `Group`, Grade DESC, ID;

Result:

ID  Group   Grade
5   A       8
4   A       7
7   B       10
9   B       9
3   B       4

In MySql 8 you could use the ROW_NUMBER or the DENSE_RANK functions for this.

edited Mar 07 '19 at 17:01

answered Mar 07 '19 at 16:48

LukStorms

28,916
5
31
45

Thanks a lot. I have 1.5 millon records an 2400 groups. Is there a way to make a sort of loop in the query? – Claudio Hernandez Mar 07 '19 at 17:02
In MySql 8 you have more options, like Recursive CTE's which is the closest to a loop in a Query. And also the window functions like ROW_NUMBER. But I'm guessing you're still on a 5.* However, one can use variables to simulate ROW_NUMBER. Have a look at [this](https://stackoverflow.com/a/54727853/4003419) previous answer of mine. Once you have a rank per `Group`, you can use it. f.e. `WHERE rnk <= 3` – LukStorms Mar 07 '19 at 17:19
Thanks a lot. I will chek this out – Claudio Hernandez Mar 08 '19 at 18:05

Jannes Botis · Answer 2 · 2019-03-09T10:05:56.940

You can

group by the Group column and
use subqueries to get the best grade ids that you want

eg:

SELECT s1.`group` AS groupId1,  (
    SELECT id
    FROM `students` s2
    WHERE s2.`group` = s1.`group`
    ORDER BY s2.grade DESC, s2.id ASC
    LIMIT 0,1
 ) AS bestGradeId,  (
    SELECT id
    FROM `students` s2
    WHERE s2.`group` = s1.`group`
    ORDER BY s2.grade DESC, s2.id ASC
    LIMIT 1,1
 ) AS best2GradeId,  (
    SELECT id
    FROM `students` s2
    WHERE s2.`group` = s1.`group`
    ORDER BY s2.grade DESC, s2.id ASC
    LIMIT 2,1
 ) AS best3GradeId
FROM `students` s1
GROUP BY s1.`group`

Select different number of records by group

2 Answers2