Often in PostgreSQL you may want to calculate the rank of values in one column, grouped by another column.
You can use the following syntax to do so:
SELECT *,
RANK() OVER(PARTITION BY team ORDER BY points) AS points_rank
FROM athletes;
This particular example will calculate the rank of each value in the points column, grouped by the team column in the table named athletes.
The ranks will be saved to a new column named points_rank.
The following example shows how to use this syntax in practice.
Example: How to Calculate Rank 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', 25);
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 | 25
10 | Heat | 33
Suppose that we would like to calculate the rank of points scored by players on each team.
We can use the following syntax to do so:
SELECT *,
RANK() OVER(PARTITION BY team ORDER BY points) AS points_rank
FROM athletes;
id | team | points | points_rank
----+------+--------+-------------
6 | Heat | 16 | 1
8 | Heat | 18 | 2
9 | Heat | 25 | 3
10 | Heat | 33 | 4
7 | Heat | 40 | 5
4 | Mavs | 9 | 1
3 | Mavs | 12 | 2
5 | Mavs | 14 | 3
1 | Mavs | 22 | 4
2 | Mavs | 30 | 5
Notice that this table contains a new column named points_rank, which assigns a rank from 1 (smallest) to 5 (largest) to players on each team based on the value in the points column.
Note that this example ranks the values in the points column in ascending order by default.
However, you can specify ORDER BY points DESC to instead rank the values in the points column in descending order.
Feel free to use similar syntax to calculate rank by group in 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 Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns
