CODEWITHSUNDEEP
sqldb2greatest-n-per-group
heisenbergman
heisenbergman

Reputation: 1449

How can I construct my SQL query to select the two latest/most recent groups of records for each key field in a table in DB2?

Given a data set for example as follows:

PERSON - the person allocating funds (key field)
EFFECTIVE DATE - the effective date of the percentage distribution
RECIPIENT - the person receiving a certain percentage of the funds
PERCENTAGE - the percentage that the person receives

(so for any given effective date, a person allocates a total of 100% to any given combination of recipients)

PERSON     EFFECTIVE DATE  RECIPIENT     PERCENTAGE 
---------- --------------- ------------- ----------- 
MONICA     2015-10-01      BARNEY        100%         +
MONICA     2015-09-01      BARNEY        50%          +
MONICA     2015-09-01      MARSHALL      20%          + 
MONICA     2015-09-01      LILY          30%          +
MONICA     2015-08-01      ROBIN         50%
MONICA     2015-08-01      TED           50%
CHANDLER   2015-10-01      ROBIN         50%          +
CHANDLER   2015-10-01      LILY          50%          +
CHANDLER   2015-07-10      BARNEY        50%          +
CHANDLER   2015-07-10      MARSHALL      50%          +
CHANDLER   2015-06-01      ROBIN         50%
CHANDLER   2015-06-01      LILY          50%
CHANDLER   2015-04-10      BARNEY        50%
CHANDLER   2015-04-10      MARSHALL      50%
ROSS       2015-10-01      MARSHALL      100%         +
ROSS       2015-09-15      BARNEY        100%         +
PHOEBE     2015-10-01      MARSHALL      20%          +
PHOEBE     2015-10-01      BARNEY        20%          +
PHOEBE     2015-10-01      LILY          20%          +
PHOEBE     2015-10-01      ROBIN         20%          +
PHOEBE     2015-10-01      TED           20%          +
PHOEBE     2015-09-01      MARSHALL      100%         +
PHOEBE     2015-08-01      BARNEY        100%
PHOEBE     2015-07-01      LILY          100%
PHOEBE     2015-06-01      ROBIN         100%
PHOEBE     2015-05-01      TED           100%

How can I construct a single SQL query that would return only the two latest sets of allocations per person at once (all of the records marked with a "+" above)? This is so that I could process the data into a display that says, for example, that:

"Monica changed allocation FROM 50% for Barney, 20% for Marshall, and 30% for Lily TO 100% for Barney."

"Chandler changed allocation FROM 50% for Barney, 50% for Marshall TO 50% for Robin, 50% for Lily."

"Ross changed allocation FROM 100% for Barney TO 100% for Marshall."

"Phoebe changed allocation FROM 100% for Marshall TO 20% for Marshall, 20% for Lily, 20% for Barney, 20% for Robin, 20% for Ted."

Upvotes: 0

Views: 81

Answers (3)

Nitin Pal
Nitin Pal

Reputation: 21

row_number is another option

select t.*
from (select t.*,
             row_number() over (partition by person order by effective_date desc) as seqnum
      from t
     ) t
where seqnum <= 2;

Upvotes: 0

PK20
PK20

Reputation: 1066

Can try like this.

select * 
from table as W 
where w.effective_date >= (
    //get 2nd max effective date 
    select max(a.effective_date) from table as a 
    where a.person = w.person
    and a.effective_date Not = (
        //get max of effective_date per person
        select max(x.effective_date) from table as x    
        where x.person = a.person)
    )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Here is one method using where:

select t.*
from t
where t.effective_date in (select t2.effective_date
                           from t t2
                           where t2.person = t.person
                           group by t2.effective_date
                           order by t2.effective_date desc
                           fetch first 2 rows only
                          );

You can also do this with dense_rank():

select t.*
from (select t.*,
             dense_rank() over (partition by person order by effective_date desc) as seqnum
      from t
     ) t
where seqnum <= 2;

Upvotes: 1

Related Questions