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 ... ... ...
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With