Issue with DISTINCT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Issue with DISTINCT

Author

Topic

sbaroux
Starting Member

9 Posts

Posted - 2005-12-13 : 11:34:43

Hello,

I run these 2 queries and I receive a different result.
The only difference is that the 2nd query contains a DISTINCT on the first sub query ... but it's not supposed to play any role as I have a global DISTINCT at the top of the query.

Where Am I wrong???

select count(distinct a.email) from
(select
a.email
from cust_analysis_FY06Q1 a,
(select distinct email from order_FY06Q1) b
where a.email = b.email ) a,
(select distinct email from store_list where global_optin = 1 and
email_bounce is null) c
where a.email = c.email;

select count(distinct a.email) from
(select
DISTINCT a.email
from cust_analysis_FY06Q1 a,
(select distinct email from order_FY06Q1) b
where a.email = b.email ) a,
(select distinct email from store_list where global_optin = 1 and email_bounce is null) c
where a.email = c.email;

For info, when I remove the condition "where global_optin = 1 and email_bounce is null" on the 2 queries, the result is the same for the 2 queries ... I'm lost

Could you help me?

Thanks

Stephanie

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-13 : 12:43:40

Please read the hint link in my sig, and post what it asks for....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 00:37:12

Distinct will give you expected result only if rows are duplicated

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources