如何在Oracle SQL中选择前1名并按日期排序? [英] How to select top 1 and ordered by date in Oracle SQL?

问题描述

有一个明确的答案如何选择排名前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

，但是它们不能一起使用(rownum不是根据trans_date生成的):

but they does not work togeather (rownum is not generated according to trans_date):

... where rownum = 1 order by trans_date desc

问题是如何选择按日期排序的前1名?

推荐答案

... where rownum = 1 order by trans_date desc

这将选择一个任意选择的记录(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).

如Ivan所示，您可以使用子查询来对记录进行排序，然后在外部查询中使用where rownum = 1保留第一条记录.但是，这是非常特定于Oracle的，并且违反了SQL标准，在该标准中子查询结果被认为是无序的(即DBMS可以忽略order by子句).

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).

因此最好使用标准解决方案.从Oracle 12c开始:

So better go with the standard solution. As of Oracle 12c:

select * 
from table_name 
order by trans_date desc
fetch first 1 row only;

在旧版本中:

select *
from
(
  select t.*, row_number() over (order by trans_date desc) as rn
  from table_name t
)
where rn = 1;

这篇关于如何在Oracle SQL中选择前1名并按日期排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！