Get the last N rows in the database in order?
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?
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.
This should work:
WITH t AS (
SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5
)
SELECT * FROM t ORDER BY record_date ASC;
If you don't want to use order:
select * from something Offset (select case when count(id)>10 then count(id)-10 end from something)
select * from (select * from log order by 1 desc limit 50) tmp order by 1
精彩评论