CODEWITHSUNDEEP
sqlsqlitepostgresql
David Wolever
David Wolever

Reputation: 154594

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

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.

Upvotes: 10

Views: 17203

Answers (1)

Mark Byers
Mark Byers

Reputation: 838796

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

Upvotes: 17

Related Questions