Results 1 to 3 of 3

Thread: Please help!

  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Please help!

    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!

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    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

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Last edited by r123456; 07-16-04 at 01:55.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •