Skip every nth result line in PostgreSQL

Question

I am looking for a way to skip rows in PostgreSQL.

In two ways I can do this:

SELECT * FROM table WHERE id % 5 = 0 

However, I would need to get consecutive lines in order to skip correctly. For example, if I get a line (with identifiers) of 0.3.5, it will not skip 4 out of 5 lines, but instead will 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 an SQL database?

Answer 1

If you are using 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 

Answer 2

If n is large enough (e.g. 1000), you can use WITH RECURSIVE queries such as:

 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 

Answer 3

Here's a general and probably rather slow solution if you don't have access to ranking functions like row_number() . Therefore, in MySQL you should 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 add them to the external and internal 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 

• SQLFiddle 1
• SQLFiddle 2

Notes:

• An internal query must have the same table and predicate references as an external query
• The internal query must count the number of rows "to" the current row from the external query. "Before" is determined by the outer query ORDER BY