CODEWITHSUNDEEP
oracle-databaseselect
CreationSL
CreationSL

Reputation: 65

Oracle: Retrieving specific group of records based by date

I have a table in oracle that I'm trying to write a query for but having a problem writing it correctly. The data of the table looks like this:

Name ID DATE
Shane 1 01JAN2023
Angie 2 02JAN2023
Shane 1 02JAN2023
Austin 3 03JAN2023
Shane 1 03JAN2023
Angie 2 03JAN2023
Tony 4 05JAN2023

What I was trying to come up with was a way to iterate over each day, look at all the records for that day and compare with the rest of the records in the table that came before it and only pull back the first instance of the record based on the ID & Date. The expected output would be:

Name ID DATE
Shane 1 01JAN2023
Angie 2 02JAN2023
Austin 3 03JAN2023
Tony 4 05JAN2023

Can anyone tell me what the query should be to accomplish this? Thank you in advance.

Upvotes: 1

Views: 33

Answers (3)

lemon
lemon

Reputation: 15492

If you don't want to use aggregation, you can use FETCH NEXT ROWS WITH TIES:

SELECT tab.* 
FROM tab
ORDER BY ROW_NUMBER() OVER(PARTITION BY Name, Id ORDER BY DATE_)
FETCH NEXT 1 ROWS WITH TIES

Output:

NAME ID DATE_
Angie 2 02-JAN-23
Austin 3 03-JAN-23
Shane 1 01-JAN-23
Tony 4 05-JAN-23

Check the demo here.

Upvotes: 0

Paul W
Paul W

Reputation: 11538

You'll need to convert your date field to a real date so it orders correctly

SELECT name,id,MIN(TO_DATE(date,'DDMONYYYY')) date
  FROM table
 GROUP BY name,id

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143003

Isn't that just

select name, id, min(date_column)
from your_table
group by name, id;

Upvotes: 0

Related Questions