Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Not getting the correct output through this query

    Hi ,

    Actually I need one row(for every customer) which has maximum registrationdate for a particular aolindividualid . If a customer registered more than once in a day , then this query is returning more than 1 rows . It is not desirable .

    The most important thing is that , the table has 200 millions of rows , so I have to consider the performance issue also .

    SELECT
    custacc.aolindividualid
    ,custacc.accountid
    ,custacc.serviceid
    ,custacc_tmp.registrationdate

    FROM
    customeraccount custacc
    ,(
    SELECT /*+ PARALLEL(customeraccount,16)*/
    aolindividualid
    ,max(registrationdate) registrationdate
    FROM
    customeraccount
    GROUP BY
    aolindividualid
    ) custacc_tmp

    WHERE
    custacc.aolindividualid = custacc_tmp.aolindividualid
    AND NVL(custacc.registrationdate,TO_DATE('12312099','m mddyyyy')) =
    NVL(custacc_tmp.registrationdate,TO_DATE('12312099 ','mmddyyyy'))

    eg,

    AOLID ACCOUNTID SERVICEID REGISTRATIONDATE
    1 5 6 7/22/2001
    1 4 2 8/23/2002
    2 1 3 1/1/2003
    2 1 2 1/1/2003


    THE OUTPUT WILL BE LIKE :

    AOLID ACCOUNTID SERVICEID REGISTRATIONDATE
    1 4 2 8/23/2002
    2 1 3 1/1/2003


    Is there any solution ?

    Thanks in advance .....
    himridul

  2. #2
    Join Date
    Feb 2004
    Location
    Eternity
    Posts
    31
    Please check the date format of registrationdate.
    If you are including time too (I used the default format mask to test this) the query will not return duplicate rows.
    Obviousness is the enemy of Correctness
    --Bertand Russel

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    If I would able to include time , then it's obvious that I won't get duplicate records.

    But I can't do that.
    himridul

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Analytic functions are good and efficient for this kind of query. Since you may get many rows with the same date and customer ID value, the ROW_NUMBER function is a good choice:

    PHP Code:
    SELECT
     custacc
    .aolindividualid
    ,custacc.accountid
    ,custacc.serviceid
    ,custacc_tmp.registrationdate
    FROM
    (
        
    SELECT
         custacc
    .aolindividualid
        
    ,custacc.accountid
        
    ,custacc.serviceid
        
    ,custacc_tmp.registrationdate
        
    ,row_number() over (partition by custacc.aolindividualid order by custacc_tmp.registrationdate desc) as rn
        FROM
        customeraccount custacc
        
    ,(
        
    SELECT /*+ PARALLEL(customeraccount,16)*/ 
        
    aolindividualid
        
    ,max(registrationdateregistrationdate 
        FROM 
        customeraccount 
        GROUP BY
        aolindividualid 
        
    custacc_tmp

        WHERE
        custacc
    .aolindividualid custacc_tmp.aolindividualid
        
    AND NVL(custacc.registrationdate,TO_DATE('12312099','mmddyyyy')) = 
        
    NVL(custacc_tmp.registrationdate,TO_DATE('12312099','mmddyyyy')) 
    )
    WHERE rn 1

Posting Permissions

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