Max from 2 columns with priority date col then the seq col Oracle

database-designoracle

I have a column xdate, column seqnumber, personID

I want 1 record per personID, where it is the most recent xdate max(xdate)
if there is multiple same max xdates i want it to look then for the max seqnumber to determine what row to pull.

obviously this doesn't work:
select max(xdate), max(seqnumber), personID From table1
Group by personID

1 person can have many records, 1 person can have the many of the same xdate, but seqnumber iterates for each xdate starting with 0 going to n

I can't quite figure this out, I can't use a correlated sub-query because there are many records. I would need to join I believe.

enter image description here

Example data above, I would want the results of the 

1st row
3rd row 
6th row

Please help,
mike

Best Answer

You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc)

select personid, xdate, seqnumber
from (
     select personid, xdate, seqnumber
          , row_number() over (partition by personid
                               order by xdate desc, seqnumber desc) as rn
     from T
) as X
where rn = 1;

Edit: added sample data and result (http://sqlfiddle.com/#!4/73ad2/4)

create table T
(   personid   int
,  xdate      date
,  seqnumber  int );

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

insert into t values (123, '2012-01-01', 0);
insert into t values (123, '2011-01-01', 0);
insert into t values (136, '2012-01-02', 1);
insert into t values (136, '2012-01-02', 0);
insert into t values (136, '2012-01-01', 0);
insert into t values (168, '2012-01-04', 2);
insert into t values (168, '2012-01-04', 1);
insert into t values (168, '2012-01-04', 0);

select personid, xdate, seqnumber
from (
    select personid, xdate, seqnumber
         , row_number() over (partition by personid
                              order by xdate desc, seqnumber desc) as rn
    from T
) x
where rn = 1;

PERSONID    XDATE   SEQNUMBER
123     January, 01 2012 00:00:00+0000  0
136     January, 02 2012 00:00:00+0000  1
168     January, 04 2012 00:00:00+0000  2
Related Question