How to retriever the First Record and Last Record in the Group, for Same Date, In Oracle SQL

Viewed 32

enter image description here

Please Suggest me, I have tried with Partition and group in Oracle SQL, but unable to get the right query

1 Answers

One option uses row_number() twice:

select primary_key, id, created_date
from (
    select
        t.*,
        row_number() over(partition by id, trunc(created_date) order by created_date  asc) rn_asc,
        row_number() over(partition by id, trunc(created_date) order by created_date desc) rn_desc
    from mytable t
) t
where 1 in (rn_asc, rn_desc)
Related