Get the last N rows in the database in order?

Question

Let's say I have the following database table:

 record_id | record_date | record_value -----------+-------------+--------------          1 | 2010-05-01  |       195.00          2 | 2010-07-01  |       185.00          3 | 2010-09-01  |       175.00          4 | 2010-05-01  |       189.00          5 | 2010-06-01  |       185.00          6 | 2010-07-01  |       180.00          7 | 2010-08-01  |       175.00          8 | 2010-09-01  |       170.00          9 | 2010-10-01  |       165.00 

I want to grab the last 5 rows with the data ordered by record_date ASC. This is easy to do with:

SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 OFFSET 4 

Which would give me:

 record_id | record_date | record_value -----------+-------------+--------------          6 | 2010-07-01  |       180.00          7 | 2010-08-01  |       175.00          3 | 2010-09-01  |       175.00          8 | 2010-09-01  |       170.00          9 | 2010-10-01  |       165.00 

But how do I do this when I don't know how many records there are and can't compute the magic number of 4?

I've tried this query, but if there are less than 5 records, it results in a negative OFFSET, which is invalid:

SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5      OFFSET (SELECT COUNT(*) FROM mytable) - 5; 

So how do I accomplish this?

Answer 1

Why don't you just order the opposite way?

SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5; 

If you don't want to flip back correctly in the application, you can nest a query and flip them twice:

SELECT *     FROM (SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5)     ORDER BY record_date ASC; 

...which turns out to be a pretty cheap operation.

Answer 2

This should work:

WITH t AS (     SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5 ) SELECT * FROM t ORDER BY record_date ASC;