How to Select Every Nth Row in PostgreSQL

PostgreSQL is a popular database system used by many organizations and websites. It supports window functions that allows you to easily perform operations on one or mote rows, that are related to your current row. Often you may need to select every nth row in PostgreSQL. Without window functions, this requires a complicated SQL query. With window functions, you can easily get this done, as we will see in our article.

How to Select Every Nth Row in PostgreSQL

Let us say you have a PostgreSQL table students(id, name) and you need to select every 5th row in it. If your ID starts from 1 and consists of sequentially incremental values 2, 3, 4, 5, … then you can get every 5th row easily using the following SELECT query.

SELECT * FROM students WHERE id % 5 = 0

In the above query, we basically select rows whose ID value is such that ID%5==0. We use modulus arithmetic operator % to determine the rows that should be selected. The modulus operator divides ID column by divider (e.g. 5) and returns the remainder. We select all those rows where remainder is 0, that is, rows with ID= 5, 10, 15, …

Depending on n in nth row, you can update 5 in the above query to another number n, depending on your requirement.

If your table’s ID column is not sequentially increasing, then you will need to use window functions as shown below.

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

In the above query, we use two SELECT queries, one nested within the other. The inner SELECT query selects all columns and also calculates row_number() column that simply assigns row number to each row. row_number() is a window function provided by PostgreSQL. We assign its output to temporary table ‘t’. The outer SELECT query basically selects all rows from table ‘t’, where the remainder of row number column modulus 5 is 0. Here also we use modulus operator as shown above. In other words, we select rows where row_number is 5, 10, 15, etc.

In this article, we have learnt how to select every nth row in PostgreSQL. You can modify it as per your requirement.

Also read:

How to Insert Text With Single Quotes in PostgreSQL
How to Get Element’s Outer HTML Using jQuery
How to Preview Image Before It is Uploaded in jQuery
How to Get Image Size & Width Using JavaScript
How to Use Multiple jQuery Versions in Same Page

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.