Select most current data in grouped set in Oracle

Question

I am writing a procedure to query some data in Oracle and grouping it:

Account  Amt Due   Last payment  Last Payment Date (mm/dd/yyyy format)
1234      10.00       5.00          12/12/2013
1234      35.00       8.00          12/12/2013
3293      15.00      10.00          11/18/2013
4455       8.00       3.00          5/23/2013
4455      14.00       5.00          10/18/2013

I want to group the data, so there is one record per account, the Amt due is summed, as well as the last payment. Unless the last payment date is different -- if the date is different, then I just want the last payment. So I would want to have a result of something like this:

Account  Amt Due   Last payment  Last Payment Date
1234      45.00      13.00          12/12/2013
3293      15.00      10.00          11/18/2013
4455      22.00       5.00          10/18/2013

I was doing something like

select Account, sum (AmtDue), sum (LastPmt), Max (LastPmtDt)
from all my tables
group by Account

But, that doesn't work for the last record above, because the last payment was only the $5.00 on 10/18, not the sum of them on 10/18.

If I group by Account and LastPmtDt, then I get two records for the last, but I only want one per account.

I have other data I'm querying, and I'm using a CASE, INSTR, and LISTAGG on another field (if combining them gives me this substring and that, then output 'Both'; else if it only gives me this substring, then output the substring; else if it only gives me the other substring, then output that one). It seems like I may need something similar, but not by looking for a specific date. If the dates are the same, then sum (LastPmt) and max (LastPmtDt) works fine, if they are not the same, then I want to ignore all but the most recent LastPmt and LastPmtDt record(s).

Oh, and my LastPmt and LastPmtDt fields are already case statements within the select. They aren't fields that I already can just access. I'm reading other posts about RANK and KEEP, but to involve both fields, I'd need all that calculation of each field as well. Would it be more efficient to query everything, and then wrap another query around that to do the grouping, summing, and selecting fields I want?

Related: HAVING - GROUP BY to get the latest record

Can someone provide some direction on how to solve this?

score 2 · Accepted Answer · answered Dec 26 '13 at 19:45

Try this:

select Account, 
       sum ( Amt_Due), 
       sum (CASE WHEN Last_Payment_Date = last_dat THEN Last_payment ELSE 0 END), 
       Max (Last_Payment_Date)
from (
  SELECT t.*,
       max( Last_Payment_Date ) OVER( partition by Account ) last_dat
  FROM table1 t
)
group by Account

Demo --> http://www.sqlfiddle.com/#!4/fc650/8

score 0 · Answer 2 · answered Dec 26 '13 at 19:43

Rank is the right idea.

Try this

select a.Account, a.AmtDue, a.LastPmt, a.LastPmtDt from (
  select Account, sum (AmtDue) AmtDue, sum (LastPmt) LastPmt, LastPmtDt, 
    RANK() OVER (PARTITION BY Account ORDER BY LastPmtDt desc) as rnk
  from all my tables
  group by Account, LastPmtDt
  ) a
where a.rnk = 1

I haven't tested this, but it should give you the right idea.

score 0 · Answer 3 · answered Dec 26 '13 at 19:43

Try this:

select Account, sum(AmtDue), sum(LastPmt), LastPmtDt
  from (select Account,
               AmtDue,
               LastPmt,
               LastPmtDt,
               max(LastPmtDt) over(partition by Account) MaxLastPmtDt
          from your_table) t
 where t.LastPmtDt = t.MaxLastPmtDt
 group by Account, LastPmtDt

Select most current data in grouped set in Oracle

3 Answers3