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?
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
(t.row-1) % 5 = 0 in order to get the first row. This is crucial to grasp, if your use-case deals with pagination, as otherwise you would skip your entire first page. Context for how I landed here is that I was looking for a way to implement an offset within a cursor-based pagination approach.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
limit 1 is closing the CTE. So you either have a CTE marked as recursive that isn't recursive, or you have an invalid CTE. But even if you get your parentheses right, you'd wind up with "LIMIT in a recursive query is not implemented". And even if that would work I doubt that this is more efficient that using a window function (and you wouldn't actually need the nested SELECT in the recursive part to begin with). As it stands your answer is plain wrong (and not working)@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
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
ORDER BY clause
What is the fastest way to get every nth row from a SQL database?Probably using windowed functions, but the most efficient implementation will of course heavily depend on the exact structure of the table(s) in question.