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.

Bassam Mehanni · Accepted Answer · 2012-10-26 00:49:47Z

18

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

edited Oct 26, 2012 at 0:49

answered Jan 9, 2012 at 16:06

Bassam Mehanni

14.9k2 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

jllopezpino Over a year ago

it sound great! does it work for a partition by multiple columns? because my table is much more complicated

Eric Over a year ago

@user337746 - Yes, partition by works exactly as a group by statement does.

Bassam Mehanni Over a year ago

yes, partition by is similar to group by clause but for the Row_Number function

Bassam Mehanni Over a year ago

@user337746 Glad I can help, please don't forget to mark the correct answer for future reference

jero Over a year ago

@BassamMehanni i tried it on oracle, there shouldn't be comma next to Department just space before Order BY and thanks for the solution.

|

Eric · Accepted Answer · 2012-01-09 16:07:29Z

5

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

answered Jan 9, 2012 at 16:07

Eric

95.7k13 gold badges117 silver badges116 bronze badges

2 Comments

MatBailie Over a year ago

Nice, and pretty novel. But wouldn't just using row_number would have less overhead?

Eric Over a year ago

@Dems - Absolutely it would. Wanted to give another way to do it if this was just an example and he needed more granular control.

symcbean · Accepted Answer · 2012-01-09 16:13:30Z

0

Something like....

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))

answered Jan 9, 2012 at 16:13

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

1 Comment

MatBailie Over a year ago

Ohhh, I get it. But it does assume that the ID field is unique, which hasn't been state. Nice latteral thinking though.

kaybee99 · Accepted Answer · 2015-05-07 14:16:28Z

0

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

edited May 7, 2015 at 14:16

kaybee99

4,7842 gold badges35 silver badges42 bronze badges

answered Jan 13, 2014 at 9:26

jj2422

3726 silver badges21 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: getting only one max row using multiple criteria

4 Answers 4

6 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related