How to Get the Top N Rows per Group in SQL?

SQL Tip: Get the first N rows of data by group

This article introduces how to efficiently extract the first N rows of data for each group from a SQL database. We will use window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() to achieve this. These functions generate unique identifiers within a specified partition and are ideal for grouping and row selection.

Use ROW_NUMBER()

ROW_NUMBER() function assigns consecutive serial numbers starting from 1 to the rows in each partition.

SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY rate DESC) AS rn
FROM h
WHERE year BETWEEN 2000 AND 2009

In this example, ROW_NUMBER() creates a new column named "rn" to assign a serial number to the rows in each id group.

Use RANK() and DENSE_RANK()

RANK() and DENSE_RANK() functions provide another way to number rows within a group. RANK() assigns distinct values ​​to rows with the same sorting, while DENSE_RANK() assigns consecutive values.

SELECT *, RANK() OVER (PARTITION BY id ORDER BY rate DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY id ORDER BY rate DESC) AS dr
FROM h
WHERE year BETWEEN 2000 AND 2009

RANK() and DENSE_RANK() create the "rk" and "dr" columns respectively, representing each row's rank and dense ranking within its id grouping.

Combined with LIMIT or TOP to limit the results

After assigning a unique identifier to each grouping, you can use the LIMIT or TOP modifier to retrieve only the specified number of rows per grouping.

SELECT *
FROM h
WHERE year BETWEEN 2000 AND 2009
ORDER BY id, rate DESC
LIMIT 5

In this example, the LIMIT modifier retrieves the first 5 rows of each id group. You can adjust the numbers as needed.

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