Often in PostgreSQL you may want to add a column to your table that displays a row number, grouped by a specific column.
You can use the following syntax to do so:
SELECT *,
row_number() OVER (PARTITION BY team ORDER BY id) AS team_rownum
FROM athletes ORDER BY id;
This particular example will add a new column to the athletes table that contains row numbers, grouped by the team column.
The row numbers will be saved to a new column named team_rownum.
The following example shows how to use this syntax in practice.
Example: How to Add Row Number 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,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 17);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+--------
1 | Mavs | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavs | 31
6 | Mavs | 17
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Suppose that we would like to add a new column that displays the row number for each team.
We can use the following syntax to do so:
SELECT *,
row_number() OVER (PARTITION BY team ORDER BY id) AS team_rownum
FROM athletes ORDER BY id;
id | team | points | team_rownum
----+---------+--------+-------------
1 | Mavs | 22 | 1
2 | Hawks | 30 | 1
3 | Kings | 12 | 1
4 | Kings | 25 | 2
5 | Mavs | 31 | 2
6 | Mavs | 17 | 3
7 | Hawks | 10 | 2
8 | Celtics | 14 | 1
9 | Hawks | 20 | 3
10 | Hawks | 26 | 4
Notice that this table contains a new column named team_rownum, which assigns a row number to each row in the table, grouped by the values in the team column.
You can use similar syntax to create a column that contains row numbers by group in your own table in PostgreSQL.
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
