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 thisDesired 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;更多推荐
发布评论