Oracle SQL: getting only one max row using multiple criteria

Question

I have this table:

Department   NAME   EMAIL         ID     DATE1         DATE2
1            John   [email protected]   74     05/04/2007    05/04/2007
1            Sam    [email protected]   23     05/04/2007    05/04/2007
1            Tom    [email protected]   46     05/04/2007    03/04/2007
1            Bob    [email protected]   23     01/01/2006
2            Tom    [email protected]   62     02/02/2000    05/05/1997

I want to get the row (only one per department) with max DATE1, but it's not unique! So if there is multiple results I want to get the max DATE2, and if there are multiple ones then the one with the biggest ID is returned.

So there result of the query would be:

1            John   [email protected]   74     05/04/2007    05/04/2007
2            Tom    [email protected]   62     02/02/2000    05/05/1997

Thank you very much.

Answer 1

You need to use the ROW_NUMBER function:

SELECT Department, NAME, EMAIL, ID, DATE1, DATE2
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Department ORDER BY DATE1 DESC, DATE2 DESC, ID DESC) AS RowNumber, 
       Department, NAME, EMAIL, ID, DATE1, DATE2
FROM MyTable ) t
WHERE RowNumber = 1