postgresql 按顺序获取数据库中的最后N行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2597098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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:
我想用 record_date ASC 排序的数据获取最后 5 行。这很容易做到：
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?
但是，当我不知道有多少条记录并且无法计算 4 的幻数时，我该怎么做？
I've tried this query, but if there are less than 5 records, it results in a negative OFFSET, which is invalid:
我试过这个查询，但如果记录少于 5 条，它会导致负的 OFFSET，这是无效的：
SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5
OFFSET (SELECT COUNT(*) FROM mytable) - 5;
So how do I accomplish this?
那么我该如何实现呢？
回答by Travis Gockel
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.
...结果证明这是一个非常便宜的操作。
回答by Zaza Zviadadze
This should work:
这应该有效：
WITH t AS (
SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5
)
SELECT * FROM t ORDER BY record_date ASC;
回答by Omíd GZ
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)
