Often in PostgreSQL you may want to select the rows with the second largest value in a specific column, by group.
You can use the following syntax to do so:
SELECT team, points
FROM (
SELECT team,
points,
dense_rank() over (partition BY team ORDER BY points DESC) AS rnk
FROM athletes
) t
WHERE rnk = 2;
This particular example will select the rows with the second largest values in the points column, grouped by the values in the team column.
Note that we first use the dense_rank() function to first assign a rank to each row based on the values in the points column in descending order, grouped by the team column.
We then use the WHERE clause to only select rows where the rank is equal to 2.
The following example shows how to use this syntax in practice.
Example: How to Find Second Largest Value per Group in PostgreSQL
Suppose that we create the following table named athletes that contains information about the 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 rows with the second highest value in the points column for each team.
We can use the following syntax to do so:
SELECT team, points
FROM (
SELECT team,
points,
dense_rank() over (partition BY team ORDER BY points DESC) AS rnk
FROM athletes
) t
WHERE rnk = 2;
team | points
------+--------
Heat | 33
Mavs | 22
Notice that the resulting table contains the rows with the second highest values in the points column for each team.
Feel free to use similar syntax to select the rows with the second highest values by group in a specific column of your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column
