Oracle SQL: Retrieving Only One Maximum Row Using Multiple Criteria

Question

I have this table:

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

I want to get a row (only one per department) with max DATE1 , but it is not unique! Therefore, if there are several results, I want to get max DATE2 , and if there are several, the one with the largest identifier is returned.

Thus, the query result will be:

 1 John [e-mail] 74 05/04/2007 05/04/2007 2 Tom [e-mail] 62 02/02/2000 05/05/1997 

Many thanks.

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 

Answer 2

Use the over clause:

 select * from ( select Department, Name, Email, ID, DATE1, DATE2, max(DATE1) over (partition by Department) as MaxDate1, max(DATE2) over (partition by Department, DATE1) as MaxDate2, max(ID) over (partition by Department, DATE1, DATE2) as MaxID from employees ) x where x.DATE1 = x.MaxDate1 and x.DATE2 = x.MaxDate2 and x.ID = x.MaxID 

Answer 3

Sort of....

 SELECT y2.* FROM (SELECT dept, MAX( TO_CHAR(date1,'YYYYHH24MISS') || TO_CHAR(date2,'YYYYHH24MISS') || id) as lastrec FROM yourtable y1 GROUP BY dept) as ilv, yourtable y2 WHERE y2.id=TO_NUMBER(SUBSTR(y2.lastrec, 21)) 

Answer 4

 SELECT * FROM ( SELECT Department, Name, Email, ID, DATE1, DATE2, max(DATE1) over (partition by Department) as MaxDate1, max(DATE2) over (partition by Department, DATE1) as MaxDate2, max(ID) over (partition by Department, DATE1, DATE2) as MaxID FROM employees ) WHERE x.DATE1 = x.MaxDate1 AND x.DATE2 = x.MaxDate2 AND x.ID = x.MaxID