Oracle SQL从有序数据集中获取第一个和最后一个记录(Oracle SQL get the first and last records from an ordered dataset)

Oracle SQL从有序数据集中获取第一个和最后一个记录(Oracle SQL get the first and last records from an ordered dataset)

我正在处理的软件需要获取有序数据集的第一个和最后一个记录。 数据集按日期列排序。

我拥有的数据：

--table "notes": -- ordered by this -- | -- V note_id date_created attribute1 attribute2 ... -- I want to get ----------------------------------------------------- 596 2014/01/20 ... ... ... -- <- this 468 2014/02/28 ... ... ... 324 2014/03/01 ... ... ... 532 2014/04/08 ... ... ... 465 2014/05/31 ... ... ... -- <- and this

期望的输出：

596 2014/01/20 ... ... ... 465 2014/05/31 ... ... ...

The software I am working on has a requirement to get the first and last records of an ordered dataset. Dataset is ordered by a date column.

The data I have:

--table "notes": -- ordered by this -- | -- V note_id date_created attribute1 attribute2 ... -- I want to get ----------------------------------------------------- 596 2014/01/20 ... ... ... -- <- this 468 2014/02/28 ... ... ... 324 2014/03/01 ... ... ... 532 2014/04/08 ... ... ... 465 2014/05/31 ... ... ... -- <- and this

Desired output:

596 2014/01/20 ... ... ... 465 2014/05/31 ... ... ...

最满意答案

您可以使用窗口功能：

select t.* from (select t.*, row_number() over (order by date_created) as seqnum, count(*) over () as cnt from t ) t where seqnum = 1 or seqnum = cnt;

在Oracle 12中，你也可以这样做：

select t.* from t order by date_created fetch first 1 rows only union all select t.* from t order by date_created desc fetch first 1 rows only;

You can use window functions:

select t.* from (select t.*, row_number() over (order by date_created) as seqnum, count(*) over () as cnt from t ) t where seqnum = 1 or seqnum = cnt;

In Oracle 12, you can also do:

select t.* from t order by date_created fetch first 1 rows only union all select t.* from t order by date_created desc fetch first 1 rows only;