Recent Dates Query

• Ramona Roman

  Old Hand

  Points: 399

  More actions

  October 11, 2004 at 1:34 pm

  #84462

   

  I have been tasked with querying one of the "payment" tables in our database to pull the most recent payment made to each account.

  The table is arranged like this:

  ID           Account #        Description        Date       Amt

  1              123456           Gas Payment     5/8/04    20.56

  2              123457          Deposit Payment 5/19/04  60.59

  3              123456           Gas Payment      8/9/04    89.54

   

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  October 11, 2004 at 3:59 pm

  #526259

  SELECT PMT1.* FROM PaymentTable PMT1 WHERE PMT1.Date = ( SELECT MAX(PMT2.Date) FROM PaymentTable PMT2 WHERE PMT2.AccountNum = PMT1.AccountNum )

   

• William Millett

  SSC Enthusiast

  Points: 147

  More actions

  October 12, 2004 at 11:11 am

  #526375

  My first reply appeared to have failed, Sorry if I'm repeating.

  If two checks, same account, same date are posted both will appear in Aaron's solution.  If that is a problem, use 2 aggregate queries (first over AccountNums' dates, then over AccountNums' MaxDates) to get one ID per account. Here's one way to do that:

  select pmt1.* from PaymentTable pmt1

  where pmt1.ID in (select max(ID) from

  (select pmt2.ID, pmt2.Account from PaymentTable pmt2 inner join (select pmt3.Account, max( pmt3.Date ) as MaxDate  from PaymentTable pmt3 group by pmt3.Account

  ) pmtAgg on pmt2.Account = pmtAgg.Account and pmt2.Date = pmtAgg.MaxDate

  ) as MaxDateIDs

  group by Account)

• julee_lovely

  SSC Journeyman

  Points: 96

  More actions

  October 12, 2004 at 11:02 pm

  #526453

  Try this

  select id,pmt2.account,pmt2.date

  from PaymentTable pmt2,

            ( select distinct account,max(Date) date

                      from PaymentTable

                      group by (account))pmt1

  where pmt2.account=pmt1.account

  and pmt2.date=pmt1.date

  Have a great day