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.
>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