CODEWITHSUNDEEP
sqlpostgresqlpagination
Martin Kadlec
Martin Kadlec

Reputation: 4975

How to select rows with id just larger than x in descending order

I would like to select 5 newer posts than post with id 15 in a descending order using only SQL. This query works but I would like to do that using just one SELECT. Any ideas?

SELECT * FROM (
            SELECT * FROM posts WHERE id > 15 ORDER BY id ASC LIMIT 5
            ) as reverse_ordered ORDER BY id DESC

The next query won't work because I want posts with ids 20-16, but this one would return the newest posts (e.g. 100-96 in case of 100 posts) 

SELECT * FROM posts WHERE id > 15 ORDER BY id DESC LIMIT 5

Gaps in ids are possible, the numbers above are just as an example.

Upvotes: 1

Views: 470

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

For the record, the query you have is the optimum and much faster than any alternative when supported with an index on id. It resolves to a single index scan. The cost for the 2nd sort is negligible. Not sure why you want a single SELECT, but it's probably a misunderstanding.

As proof of concept, while we entertain this wild goose chase: you asked for SELECT *, not for SELECT id:

SELECT (unnest((array_agg(p ORDER BY p.id))[1:5])).*
FROM   posts p
WHERE  p.id > 15
ORDER  BY id DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Most human beings would not consider this simpler. But, it doesn't use a subquery:

select unnest( (array_agg(p.id order by p.id asc)::int[])[1:5]) as id
from posts p
where p.id >= 15
order by id desc;

The method with the subquery should be superior performance-wise.

Upvotes: 3

Related Questions