如何在 Oracle SQL 中选择前 1 位并按日期排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44430702/
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
How to select top 1 and ordered by date in Oracle SQL?
提问by Ivan Gerasimenko
There is a clear answer how to select top 1:
有一个明确的答案如何选择前 1 名：
select * from table_name where rownum = 1
and how to order by date in descending order:
以及如何按日期降序排序：
select * from table_name order by trans_date desc
but they does not work togeather (rownumis not generated according to trans_date):
但它们不能一起工作（rownum不是根据 生成的trans_date）：
... where rownum = 1 order by trans_date desc
The question is how to select top 1 also ordered by date?
问题是如何选择按日期排序的前 1 个？
回答by Thorsten Kettner
... where rownum = 1 order by trans_date desc
This selects one record arbitrarily chosen (where rownum = 1) and then sorts this one record (order by trans_date desc).
这将选择任意选择的一条记录 ( where rownum = 1)，然后对这一条记录 ( order by trans_date desc)进行排序。
As shown by Ivan you can use a subquery where you order the records and then keep the first record with where rownum = 1in the outer query. This, however, is extremely Oracle-specific and violates the SQL standard where a subquery result is considered unordered (i.e. the order by clause can be ignored by the DBMS).
如 Ivan 所示，您可以使用子查询对记录进行排序，然后where rownum = 1在外部查询中保留第一条记录。然而，这是非常特定于 Oracle 的，并且违反了 SQL 标准，其中子查询结果被认为是无序的（即 DBMS 可以忽略 order by 子句）。
So better go with the standard solution. As of Oracle 12c:
所以最好使用标准解决方案。从 Oracle 12c 开始：
select *
from table_name
order by trans_date desc
fetch first 1 row only;
In older versions:
在旧版本中：
select *
from
(
select t.*, row_number() over (order by trans_date desc) as rn
from table_name t
)
where rn = 1;
回答by a_horse_with_no_name
You can use window functions for that:
您可以为此使用窗口函数：
select t.*
from (
select *,
min(trans_date) over () as min_date,
max(trans_date) over () as max_date
from the_table
) t
where trans_date = min_date
or trans_date = max_date;
Another option would be to join on the derived table
另一种选择是加入派生表
select t1.*
from the_table
join (
select min(trans_date) over () as min_date,
max(trans_date) over () as max_date
from the_table
) t2 on t1.trans_date = t2.min_date
or t1.trans_date = t2.max_date;
Not sure which one would be faster, you need to check the execution plan
不确定哪个会更快，您需要检查执行计划
回答by jarlh
Modern Oracle versions have FETCH FIRST:
现代 Oracle 版本具有FETCH FIRST：
select * from table_name order by trans_date desc
fetch first 1 row only
回答by Ivan Gerasimenko
There should be subquery so the combination rownum & ordercould work:
应该有子查询，以便组合rownum & order可以工作：
select * from (select * from table_name order by trans_date desc) AS tb where rownum = 1
