How to Select the Top N Rows per Group Ordered by a Specific Column in PostgreSQL?

Group restriction in PostgreSQL: Select top N rows in each group sorted by custom column

In order to retrieve the top N rows in each group based on a custom column order, PostgreSQL provides a solution for users (especially those using PostgreSQL 8.4 and above).

Consider the example table containing the columns 'id', 'section_id' and 'name':

id	section_id	name
1	1	A
2	1	B
3	1	C
4	1	D
5	2	E
6	2	F
7	3	G
8	2	H

Based on the data provided, the desired output is to select the first two rows (sorted by 'name') for each 'section_id'.

Solutions for PostgreSQL 8.4 and above

<code class="language-sql">SELECT
  *
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r,
    t.*
  FROM
    xxx t
) x
WHERE
  x.r <= 2;</code>

This advanced solution utilizes the 'ROW_NUMBER()' function with 'PARTITION BY' and 'ORDER BY' clauses to achieve the desired result set. Each row in the 'xxx' table is assigned a rank within its respective 'section_id' group, with rows sorted by 'name'. By subsequent selection based on the condition 'x.r <= 2' we effectively limit the number of rows returned in each group.

The above is the detailed content of How to Select the Top N Rows per Group Ordered by a Specific Column in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!