Can you also send the explain plan outputs for the distinct query? Ashish
________________________________ From: Namit Jain [mailto:[email protected]] Sent: Monday, July 06, 2009 10:03 AM To: [email protected] Subject: RE: distinct with union all Nothing special for these characters. select username, count(1) from (select user as username from page_views pv union all select name as username from users u) ur group by username; should return the counts. When you see the duplicate usernames, can you send them - From: Rakesh Setty [mailto:[email protected]] Sent: Monday, July 06, 2009 9:45 AM To: [email protected] Subject: RE: distinct with union all Hi Amr, The group by clause that you suggest shows that grouping is happening as the count column suggests. Perhaps it is not happening across the tables? I see characters like \, [, _, ^, etc in username. Are these considered as special characters? If so, is there any workaround to deal with this? Thanks, Rakesh ________________________________ From: Amr Awadallah [mailto:[email protected]] Sent: Thursday, July 02, 2009 5:53 PM To: [email protected] Subject: Re: distinct with union all make sure you don't have any leading or trailing spaces (or special characters) for the usernames being extracted. also to debug, try to do a select username, count(1) then group by username -- amr Rakesh Setty wrote: Yes, I am getting duplicate usernames. ________________________________ From: Namit Jain [mailto:[email protected]] Sent: Thursday, July 02, 2009 1:25 PM To: [email protected]<mailto:[email protected]> Subject: RE: distinct with union all Are you getting duplicate usernames ? From: Rakesh Setty [mailto:[email protected]] Sent: Thursday, July 02, 2009 12:37 PM To: [email protected]<mailto:[email protected]> Subject: distinct with union all Hi, I have a query like select distinct username from (select user as username from page_views pv union all select name as username from users u) ur; But I see that result is not actually distinct. Am I missing something here? Thanks, Rakesh
