SQL: select N “most recent” rows in ascending order

sql sqlite postgresql

16,183

Use an inner select to select the correct rows, and an outer select to order them correctly:

SELECT timestamp, message
FROM
(
     SELECT *
     FROM your_table
     ORDER BY timestamp DESC
     LIMIT 3 
) T1
ORDER BY timestamp

16,183

Author by

David Wolever

Co-founder of https://akindi.com, python, http, vim, javascript, postgres, bash, motorcycles, toronto, startups Twitter: http://twitter.com/wolever Email: [email protected] Blog: http://blog.codekills.net GPG: B230230D GitHub: https://github.com/wolever StackOverflow: http://stackoverflow.com/users/71522/wolever Resume: http://careers.stackoverflow.com/wolever LinkedIn: http://www.linkedin.com/in/wolever Reddit: http://www.reddit.com/user/wolever Callsign: VA3WVR

Updated on July 18, 2022

Comments

David Wolever almost 2 years
For example, if my data look like this:
```
timestamp | message
100 | hello
101 | world
102 | foo
103 | bar
104 | baz
```
How can I select the three most recent rows — 102, 103, 104 — in ascending order?

The obvious (to me) … LIMIT 3 ORDER BY timestamp DESC will return the correct rows but the order is incorrect.
David Wolever over 12 years

Hrm… I don't have a running instance of postgres handy to try that with, but SQLite's EXPLAIN suggests that will result in a sort (two if the column isn't indexed). Is there any way this redundant sort can be avoided?
Brandon Rhodes over 12 years

No; SQL does not have a "reverse" operation. But maybe the language into which you are pulling the results does?
Matthew Wood over 12 years

@David: Wait, are you fretting over sorting three records? ;)
David Wolever over 12 years

Yes, I have ended up just reversing the results in Python… I'm more curious because I'd like to learn something new about SQL :)