LIMIT SQL query per GROUP BY

Question

I have 20 classroom, each classroom has 50 students.

is it possible to find 10 best students in each classroom in a single query statement?

The table "klas":

int id
int cla_id
int stu_id
int final_score

Answer 1

In most databases, you can do:

select k.*
from (select k.*,
             row_number() over (partition by cla_id order by final_score desc) as seqnum
      from klas
     ) k
where seqnum <= 10;

This retrieves 10 students per class with the highest scores. It doesn't work in databases like MySQL and MS Access that do not support ANSI standard window functions like row_number().

The row_number() function applies a sequential numbering to rows. The numbering starts over again for each cla_id in your case -- because of the partition by clause. The row with number "1" is the one with the largest final_score -- because of the order by final_score desc. This can also be applied to aggregation functions like min() and sum(). In that case, it provides the aggregated value, but on every row of the original data set (the equivalent of doing an aggregation and join, but typically more efficiently).

Answer 2

If the 10th best student has the same score as the 11th or 12th, you don't want to return only number 10, but also 11 and 12. So in that case it's better to use RANK(), like this:

;WITH ranking
   AS
(
    SELECT *
          ,RANK() OVER (PARTITION BY cla_id
                            ORDER BY final_score DESC) AS rank
      FROM klas
)
  SELECT cla_id
        ,rank
        ,stu_id
    FROM ranking
   WHERE rank <= 10
ORDER BY cla_id
        ,rank