I have a table called SUBACCOUNT that looks like this:

SUBACCOUNT_ID CREATED PERCENT
922 18/04/2002 100
922 30/09/2002 54
922 30/09/2002 46
922 17/09/2003 0
922 18/09/2003 100
922 28/01/2004 0
922 29/01/2004 33
922 29/01/2004 67
925 18/04/2002 100

I need to select the most recent records where Percent adds to 100 for each subaccount_id. For subaccount_id=922, that would mean the last two records. For subaccount_id=925, it might just be the last record that Percent adds to 100. Thanks for your help!

what if no records add up to 100. I mean what if you have something like this
922 18/09/2003 99
922 28/01/2004 0
922 29/01/2004 33
922 29/01/2004 66
925 18/04/2002 100

Then what will be the output for #922, or am I reading this wrong and the numbers will add up to exactly 100 everytime

Hi,

SELECT *
FROM
(SELECT SUBACCOUNT_ID, CREATED, RANK() OVER (PARTITION BY SUBACCOUNT_ID ORDER BY CREATED DESC) AS RK
FROM SUBACCOUNT
GROUP BY SUBACCOUNT_ID, CREATED
HAVING SUM(PERCENT) = 100) V
WHERE V.RK <= 2
ORDER BY V.SUBACCOUNT_ID, V.RK

Alternatively,

SELECT *
FROM SUBACCOUNT S
WHERE S.CREATED IN
(SELECT CREATED
FROM
(SELECT SUBACCOUNT_ID, CREATED
FROM SUBACCOUNT
GROUP BY SUBACCOUNT_ID, CREATED
HAVING SUM(PERCENT) = 100
ORDER BY CREATED DESC) V
WHERE rownum <= 2 AND V.SUBACCOUNT_ID = S.SUBACCOUNT_ID)
ORDER BY S.SUBACCOUNT_ID, S.CREATED DESC

Thank You.