CODEWITHSUNDEEP
sqldb2-400
Bala
Bala

Reputation: 813

Select latest data based on date column

I have a table ACCT,

COMPANY    ACCOUNT    EFFDT        NAME
25         1001       2015-12-01   ABC Ltd.
25         1001       2012-01-01   ABC Inc
30         1001       2012-01-01   ABC Inc
10         1001       2012-01-01   ABC Inc
10         3800       2011-05-01   RAC Corp
25         3800       2011-05-01   RAC Corp
30         3800       2011-05-01   RAC Corp

I am trying to get the last record for a set of accounts based on EFFDT column for a account #

Result I am looking for is,

| 25  | 1001  | 2015-12-01   | ABC Ltd.
| 30  | 3800  | 2011-05-01   | RAC Corp

I tried using,

select * 
  from acct t1 
    inner join (
        select account as a, max(effdt) as b, max(company) as c 
          from acct 
          where account in (1001, 3800) 
          group by account
        ) t2 
      on t1.account = t2.a 
        and t1.effdt = t2.b 
        and t1.company = t2.c;

but I only get,

| 30  | 3800  | 2011-05-01   | RAC Corp

Upvotes: 0

Views: 44

Answers (2)

isaace
isaace

Reputation: 3429

This should work:

select max(a.company), a.account, a.effdt, a.name from acct a
join(select account, max(EFFDT) as m  
    from acct 
    group by account)
b on a.account = b.account and a.EFFDT = m  
group by a.account, a.effdt, a.name

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

using ROW_NUMBER

https://chartio.com/resources/tutorials/how-to-use-row_number-in-db2/

  SELECT
      company,
      account,
      effdt,
      name
    FROM (SELECT
      company,
      account,
      effdt,
      name,
      ROW_NUMBER() OVER (PARTITION BY account ORDER BY effdt DESC, company DESC) rn  
    FROM acct) x
    WHERE x.rn = 1

Upvotes: 2

Related Questions