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

Getting the Top Two Rows for Each Group in SQL

SQL queries enable users to extract specific data from tables, but what if you need to select the top two rows for each group? This scenario can be encountered when you want to analyze the highest-scoring records or retrieve multiple values within a group.

Let's consider the following table as an example:

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

To retrieve the highest two scores for each name, we can utilize the following SQL query:

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

Here's how the query works:

• The outer query (SELECT * FROM test s) selects all rows from the test table.
• The inner query, enclosed in parentheses, calculates the count of rows in the test table with a given name and score greater than or equal to the current row's score (f.score >= s.score).
• The WHERE clause of the outer query filters the rows based on the count from the inner query. Only rows where the count is less than or equal to 2 are selected. This ensures that we get the top two scores for each group.

The expected output for this query is as follows:

NAME	SCORE
willy	2
willy	3
zoe	5
zoe	6

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