Often in PostgreSQL you may want to select the top N records in a table grouped by a specific column.
You can use the following syntax to do so:
SELECT *
FROM (
SELECT *,
row_number() over (partition BY team ORDER BY points DESC) AS rank
FROM athletes
) t
WHERE rank <= 3;
This particular example will select the top 3 records from the table named athletes based on the values in the points column.
To select a different number of top N records, simply change the 3 to a different number.
The following example shows how to use this syntax in practice.
Example: How to Select Top N Records by Group in PostgreSQL
Suppose that we create the following table named athletes that contains information about points scored by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
3 | Mavs | 12
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
7 | Heat | 40
8 | Heat | 18
9 | Heat | 16
10 | Heat | 33
Suppose that we would like to select the top 3 players with the highest values in the points column for each team.
We can use the following syntax to do so:
SELECT *
FROM (
SELECT *,
row_number() over (partition BY team ORDER BY points DESC) AS rank
FROM athletes
) t
WHERE rank <= 3;
id | team | points | rank
----+------+--------+------
7 | Heat | 40 | 1
10 | Heat | 33 | 2
8 | Heat | 18 | 3
2 | Mavs | 30 | 1
1 | Mavs | 22 | 2
5 | Mavs | 14 | 3
The resulting table contains the top 3 players from each team with the highest values in the points column.
Feel free to use similar syntax to select the top N records by group from your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Top N Percent of Records
PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
