Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading