How to Retrieve the Top Two Scores for Each Group in SQL?

Retrieve the Top Two Scores for Individual Groups in SQL

In a relational database, extracting meaningful data from large datasets often involves grouping records based on specific characteristics. When retrieving data from grouped sets, it's common to obtain the highest or lowest values within each group using aggregation functions. However, what if you need to select the top N rows for each group?

Consider the following table with student names and their corresponding scores:

NAME	SCORE
willy	1
willy	2
willy	3
zoe	4
zoe	5
zoe	6

The aggregation function for grouping only allows you to obtain the highest score for each name. To retrieve the top two scores for each student, a different approach is required.

The following query achieves this using a subquery to track the rank of each student's scores within their respective groups:

SELECT *
FROM test s
WHERE 
        (
            SELECT COUNT(*) 
            FROM test f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2

Breaking Down the Query:

• The outer query, SELECT * FROM test s, retrieves all rows from the test table and aliases them as s.

• The subquery, enclosed in parentheses, calculates the rank of each student's score:

  • SELECT COUNT(*) FROM test f: This part counts the number of rows in the test table where the name (f.name) matches the name of the current row (s.name) and the score (f.score) is greater than or equal to the score of the current row (s.score).
• The WHERE clause applies a filter to the outer query, selecting only those rows whose rank (as determined by the subquery) is less than or equal to 2.

Output:

Executing this query will return the following results:

NAME	SCORE
willy	2
willy	3
zoe	5
zoe	6

This query effectively retrieves the top two scores for each student, providing a more comprehensive view of their performance compared to using a simple MAX() aggregation.

The above is the detailed content of How to Retrieve the Top Two Scores for Each Group in SQL?. For more information, please follow other related articles on the PHP Chinese website!