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:

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:

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

CodePudding user response：

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

CodePudding user response：

Isn't that just

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

CodePudding user response：

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:

Check the demo here.