How to Select the Top Two Rows for Each Group in SQL?

Selecting the Top Two Rows in Each SQL Group

In SQL, a common task is to retrieve only the top rows for each group. However, the standard aggregation functions for grouping (such as MAX() and MIN()) only allow for the selection of the highest or lowest value.

Query for Selecting the Top Two Scores per Name:

To address this challenge, a more complex query is required. Here's an example of how to select the top two scores for each name in the table provided:

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

Explanation:

• The outer query (SELECT * ...) retrieves all rows from the test table.
• The subquery (SELECT COUNT(*) ...) determines how many records exist for each name in the test table with a score equal to or greater than the current row's score.
• The WHERE clause then filters the rows in the outer query by ensuring that the count from the subquery is less than or equal to 2.

Example Output:

The query above returns the following output:

NAME    SCORE
-----------------
willy       2
willy       3
zoe         5
zoe         6

This output includes the top two scores for each name in the table, as requested.

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