CODEWITHSUNDEEP
sqlpostgresql
RobotRock
RobotRock

Reputation: 4459

Skip every nth result row in PostgreSQL

I'm looking for a way to skip rows in PostgreSQL.

Two ways I could do this are using:

SELECT * FROM table WHERE id % 5 = 0

However I'd have to fetch sequential rows to properly skip. For instance if I fetch row (with ids) 0,3,5, it would not skip 4 out of 5 rows, but instead result in (ids) 0 and 5.

Or skip outside of SQL:

$count = 0;
while($row = progres_fetch_row($result))
  if ($count++ % 5 == 0)
     // do something

What is the fastest way to get every nth row from a SQL database?

Upvotes: 31

Views: 21328

Answers (4)

Prakash Dahal
Prakash Dahal

Reputation: 4875

@Guillaume Poussel works fine for skipping nth row, but if we want to remove n elements between 0 to x range, it wont include the 0th row.

So, if we want to only include the every 5th element starting from 0 index, this is the solution:

SELECT t.*
FROM (
  SELECT *, row_number() OVER() AS row
  FROM dcia.test
) t
WHERE (t.row-1) % 5 = 0

Original indexes:

0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

Indexes after the above statement:

0,5,10,15

Upvotes: 0

konrad
konrad

Reputation: 11

If n is large enough (like 1000), you can consider using WITH RECURSIVE queries like:

WITH RECURSIVE foo(name) AS
    (SELECT name FROM people ORDER BY name LIMIT 1)
  UNION
    (SELECT
      (SELECT name FROM people
        WHERE name >= foo.name
        ORDER BY name OFFSET 1500 LIMIT 1)
      FROM foo LIMIT 1
    )
SELECT * FROM foo

Upvotes: 1

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

If you use PostgreSQL, you can use row_number():

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

Upvotes: 53

Lukas Eder
Lukas Eder

Reputation: 220887

Here's a generic and probably quite slow solution in case you don't have access to ranking functions, such as row_number(). So in MySQL, you would write:

select * 
from x x1
where (
  select count(*) 
  from x x2 
  where x2.id <= x1.id
) % 5 <> 0
order by x1.id asc

If you want to add additional predicates, just be sure to add them to both outer and inner query:

select * 
from x x1
where x1.id % 2 = 0
and (
  select count(*) 
  from x x2 
  where x1.id % 2 = 0
  and x2.id <= x1.id
) % 5 <> 0
order by x1.id asc

Remarks:

  • The inner query has to have the same table references and predicaets as the outer query
  • The inner query needs to count the number of rows "before" the current row from the outer query. "Before" is defiend by the outer query's ORDER BY clause

Upvotes: -1

Related Questions