Search code examples
sqldatedb2comparisonunique

SQL how to return the latest date

In the code below, I have been getting multiple rows for col0.

col0 is not unique, so for example - col0 might have four rows with the value 5 and two rows with the value 7, etc.

I want to return just one row for each value in col0. Assuming numToDate is a UDF which is self explanatory (to ISO), I want to return one row for each value in col0 where col3 is the most recent date.

SELECT col0, col1, numToDate(col2), numToDate(col3)
FROM myTable
where col1 = 'someValue';
Solution

  • You can use row_number():

    select col0, col1, numToDate(col2), numToDate(col3)
from (select t.*,
             row_number() over (partition by col0 order by numToDate(col3) desc) as seqnum
      from mytable t
     ) t
where seqnum = 1;