Avatar of pm-archtect
pm-archtect
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Oracle - RANK Dates to select last for each day

Hi

I need to find only the last record for each date/time for each unitid in the table.

For each unitid there are many records all submitted over a timespan during the day (every day).  Only the last submission on a given date/time for each unitid is required.

If you look at the attached sample, it shows the starting point and the desired result.

The desired result is one row for each unitid for each day - and that row is the last date/time that an entry was submitted.

I think I need to rank these by date in decending order where the rank < 2 but I'm not sure how to create the window over unitid and date.

Thanks
RANK-Sample-Data.xlsx
Oracle Database

Avatar of undefined
Last Comment
pm-archtect

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

note, you could use RANK or DENSE_RANK instead of ROW_NUMBER, but since you only need one,  the extra sorting/numbering features of those don't really apply
Geert G

don't you need to partition by day ? (the every day part of the Q text)

select * from
(select t.*,row_number() over(partition by unitid, to_char('yyyymmdd', datereceived) order by to_char('yyyymmdd', datereceived) desc, datereceived desc) r
from yourtable)
where r = 1
pm-archtect

ASKER
Thank you for your help.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck