Get the last N rows in the database in order?
- by Kristopher
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?
