SQL LIMIT, but from the end

Question

Can you LIMIT a query from the end of the results, rather than from the beginning? In particular, I'm looking for a solution w/ Postgresql, if that makes a difference.

Allow me to clarify with an example.

Let's say I want to return the 3 oldest people in my people table, but in ascending order of age. The best way I know how to select the 3 people returns the correct records, but in the reverse order:

SELECT * FROM people
ORDER BY age DESC
LIMIT 2

See Kshitij's answer - sort in the opposite direction, take TOP 2, then sort back again — marc_s, Jun 26 '12 at 05:26

score 9 · Accepted Answer · edited Jun 27 '12 at 12:06

9

should be this way-

SELECT * FROM (
SELECT *
FROM PEOPLE
ORDER BY AGE DESC
LIMIT 3 ) X
ORDER BY AGE ASC

edited Jun 27 '12 at 12:06

kgrittn

18,113
3
39
47

answered Jun 26 '12 at 04:34

Kshitij

8,474
2
26
34

I edited the query to add an alias after the subquery; I'm pretty sure that's required by PostgreSQL. – kgrittn Jun 27 '12 at 12:07

SQL LIMIT, but from the end

1 Answers1

Linked