Oracle SQL 从有序数据集中获取第一条和最后一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25684794/
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
Oracle SQL get the first and last records from an ordered dataset
提问by sampathsris
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 ... ... ...
回答by Gordon Linoff
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:
在 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;
回答by neshkeev
If I got it right, try this:
如果我猜对了，请尝试以下操作：
select t1.*
from YOUR_TABLE t1
, (
select min(note_id) keep(dense_rank first order by date_created) min_val
, max(note_id) keep(dense_rank last order by date_created) max_val
from YOUR_TABLE
) t2
where t1.note_id = t2.min_val
or t1.note_id = t2.max_val
回答by SAROJ SAHU
Select * from emp where rowid =(select min(rowid) from emp)
Union
Select * from emp where rowid =(select max(rowid) from emp);
