PostgreSQL: How to Select Every Nth Row in Table

Often in PostgreSQL you may want to select every Nth row in a table.

You can use the following syntax to do so:

SELECT t.*
FROM (
  SELECT *, row_number() OVER(ORDER BY id ASC) AS row
  FROM athletes
) t
WHERE t.row % 3 = 0;

This particular example will select every third row from the table named athletes.

Here is how this syntax works:

• The inner SELECT statement adds a new column named row that contains the row number of each row in the table.
• The outer SELECT statement selects all rows WHERE the value in the row column produces no remainder when divided by 3.

The following example shows how to use this syntax in practice.

Example: How to Select Every Nth Row 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,
  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', 16);
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    |     16
  7 | Hawks   |     10
  8 | Celtics |     14
  9 | Hawks   |     20
 10 | Hawks   |     26

Suppose that we would like to select every third row from the table.

We can use the following syntax to do so:

SELECT t.*
FROM (
  SELECT *, row_number() OVER(ORDER BY id ASC) AS row
  FROM athletes
) t
WHERE t.row % 3 = 0;

 id | team  | points | row 
----+-------+--------+-----
  3 | Kings |     12 |   3
  6 | Mavs  |     16 |   6
  9 | Hawks |     20 |   9

The resulting table contains every third row from the original table.

Feel free to change the 3 to a different number to return a different nth row from the table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Rows where Column Contains Number
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String