Not getting the correct output through this query

[himridul]

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','mmddyyyy')) =
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 .....

 

[jad]

does:

SELECT custacc.aolindividualid, custacc.accountid, custacc.serviceid, custacc_tmp.registrationdate
FROM customeraccount custacc
WHERE NVL(custacc.registrationdate, TO_DATE('12312099', 'mmddyyyy')) = 
      NVL(select max(registrationdate)
           from customeraccount
           where aolindividualid = custacc.aolindividualid), TO_DATE('12312099', 'mmddyyyy'))

work for you?