1

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

1
  • "obviously this doesn't work...": I think you did not choose your example data very well because the data that is selected by your query from the example data has the properties you want.
    – miracle173
    Aug 22 '14 at 5:05
1

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
7
  • I wasn't expecting anything like this, I can't get this to spit out any results.. but I was hoping for something that used the max function and joins/sub queries
    – user45961
    Aug 21 '14 at 19:46
  • What DBMS are you using?
    – Lennart
    Aug 21 '14 at 19:47
  • Oracle is my DBMS, I would like to do a filer and get the max date for a person, then i would like to do another max on the sequence. I don't want to use a correlated sub-query though.
    – user45961
    Aug 21 '14 at 20:03
  • Recent versions of Oracle supports these functions, but I added a vanilla solution anyhow. When you say that it does not spit out any results, do you mean that there is an error or that there is no rows in the output?
    – Lennart
    Aug 21 '14 at 20:08
  • 1
    Create an sql-fiddle and I'll have a look.
    – Lennart
    Aug 21 '14 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.