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

Top N Rows for Each Group in SQL

In SQL, selecting the top rows for each group can be accomplished using window functions. Window functions allow for calculations to be performed on a set of rows, known as a window, that are defined by a specified ordering and partition. In this case, we want to select the top two rows for each group, which can be achieved using the following steps:

1. Partition the data: Define the groups for which you want to select the top rows using the PARTITION BY clause. In this example, we want to group the data by the NAME column.
2. Rank the rows within each group: Use the ROW_NUMBER() window function to assign a sequential number to each row within each group, starting from 1. This will provide us with the rank of each row within its group.
3. Filter the top rows: Apply a filter to select only the rows that have a rank of 1 or 2. This will give us the top two rows for each group.

Here is an example query implementing these steps:

SELECT *
FROM (
    SELECT *, RANK() OVER (PARTITION BY NAME ORDER BY SCORE DESC) AS row_rank
    FROM test
) AS ranked
WHERE row_rank <= 2

This query will produce the desired output, as shown below:

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

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