Often in PostgreSQL you may want to select a random row from each group in a table.
You can use the following syntax to do so:
SELECT DISTINCT ON (team) *
FROM athletes
ORDER BY team, random();
This particular example will select a random row for each unique value in the team column of the athletes table.
Note: The row that could be returned will potentially be different each time you run this query since the random() function always selects a random row each time.
The following example shows how to use this syntax in practice.
Example: How to Select Random Row from Each Group in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Kings', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
id | team | points | assists
----+-------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Kings | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to select one random player from each team.
We can use the following syntax to do so:
SELECT DISTINCT ON (team) *
FROM athletes
ORDER BY team, random();
id | team | points | assists
----+-------+--------+---------
9 | Hawks | 20 | 8
8 | Kings | 14 | 13
6 | Mavs | 16 | 3
The resulting table contains one random row selected for each team: Hawks, Kings and Mavs.
Feel free to use similar syntax to select a random row from each group in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Generate a Random Integer
PostgreSQL: How to Generate Random Dates
PostgreSQL: How to Select Random Value from Array
PostgreSQL: How to Update Each Row with Random Value
