Often in PostgreSQL you may want to select the top N percent of records in a table based on values in a specific column.
You can use the following syntax to do so:
SELECT * FROM athletes ORDER BY points DESC
LIMIT (SELECT (count(*) / (100/20)) FROM athletes);
This particular example will select the top 20% of records from the table named athletes based on the values in the points column.
To select a different percent of top N records, simply change the 20 to a different number.
Note that we use the first SELECT statement to select all records from the table, ordered by the values in the points column from greatest to least.
We then use the LIMIT statement to limit the results to only show the top N percent of records.
The following example shows how to use this syntax in practice.
Example: How to Select Top N Percent of Records 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, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Celtics', 9);
INSERT INTO athletes VALUES (0005, 'Heat', 14);
INSERT INTO athletes VALUES (0006, 'Cavs', 16);
INSERT INTO athletes VALUES (0007, 'Magic', 40);
INSERT INTO athletes VALUES (0008, 'Thunder', 18);
INSERT INTO athletes VALUES (0009, 'Grizzlies', 16);
INSERT INTO athletes VALUES (0010, 'Warriors', 33);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+-----------+--------
1 | Mavs | 22
2 | Hawks | 30
3 | Kings | 12
4 | Celtics | 9
5 | Heat | 14
6 | Cavs | 16
7 | Magic | 40
8 | Thunder | 18
9 | Grizzlies | 16
10 | Warriors | 33
Suppose that we would like to select the top 20% of players with the highest values in the points column of the table.
We can use the following syntax to do so:
SELECT * FROM athletes ORDER BY points DESC
LIMIT (SELECT (count(*) / (100/20)) FROM athletes);
id | team | points
----+----------+--------
7 | Magic | 40
10 | Warriors | 33
The resulting table contains the top 20% of players from the original table who had the highest values in the points column.
Since the original table had 10 total records, the top 20% of records is equal to the top 2 records.
To select a different percentage of top N records, we can change the 20 to a different number.
For example, we can use the following syntax to instead select the top 30% of records from the table based on values in the points column:
SELECT * FROM athletes ORDER BY points DESC
LIMIT (SELECT (count(*) / (100/30)) FROM athletes);
id | team | points
----+----------+--------
7 | Magic | 40
10 | Warriors | 33
2 | Hawks | 30
The resulting table contains the top 30% of players from the original table who had the highest values in the points column.
Feel free to use similar syntax to select the top N percent of records from your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Date from a Timestamp
