Oracle SQL: Selecting a single row with the latest date between multiple columns
I think the structure you want is this:
select idfrom (SELECT ID FROM EXAMPLE_TABLE order by GREATEST(CREATE_DATE, UPDATE_DATE) desc )where rownum = 1However, I suppose it is possible for one of the dates to be NULL. If so:
select idfrom (SELECT ID FROM EXAMPLE_TABLE order by coalesce(GREATEST(CREATE_DATE, UPDATE_DATE), CREATE_DATE) desc )where rownum = 1These queries are ordering by the larger of the two values on each row, and then selecting the maximum value.
Create a composite of all the ID's with all the date fields and then Max them with a rownum restirction to only get the first one.
WITH A AS ( SELECT ID, Max(FirstDateField) AS value FROM TableName WHERE ROWNUM=1 GROUP BY ID ORDER BY Max(FirstDateField) DESC UNION ALL SELECT ID, Max(SecondDateField) AS value FROM TableName WHERE ROWNUM=1 GROUP BY ID ORDER BY Max(SecondDateField) DESC)SELECT ID FROM AWHERE ROWNUM=1ORDER BY Value DESC