Distinct Query Quandary - 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

General SQL Server Forums

New to SQL Server Programming

Distinct Query Quandary

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-10-10 : 17:58:19

Here's my query:

Select top 500 email, custnumber from MailingList order by email

There may be some duplicate email addresses with different custnumbers. What would the query look like to only show records with unique email addresses, but still return the unique custnumbers in the query.

Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-10 : 18:35:07

Do you mean that the email appears exactly once in MailingList? Try this

SELECT t1.email,t1.custnumber
FROM MailingList t1
INNER JOIN
(select email,[nbr] = count(*)
from MailingList
group by email
having count(*) = 1
)t2
ON
t1.email = t2.email

Jim

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-10-10 : 19:00:52

No, I'm trying to show only unique email addresses but also return the unique custnumber. So the email address still needs to show (but only once) if it is duplicated. Sorry for not making that clear. Any further ideas would be greatly appreciated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 19:01:59

what should


Email          Cust#
-----          -----
abc@abc.com    1
abc@abc.com    2
abc@abc.com    3

return? you have not clearly stated your requirements.

- Jeff
http://weblogs.sqlteam.com/JeffS

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-10-10 : 19:06:56

Sorry, that should return 1 record (the first one).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 19:23:10

SELECT Email, MIN(CustNo) AS CustNo
FROM YourTable
GROUP BY Email
ORDER BY Email

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-10-10 : 19:39:57

That's it! Thanks! Just curious as to why MIN(CustNo) is used.

I tried with no luck.

SELECT Email, CustNo
FROM YourTable
GROUP BY Email
ORDER BY Email

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 21:02:30

maybe this will help clear things up:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 00:19:55

"I tried with no luck.

SELECT Email, CustNo
FROM YourTable
GROUP BY Email
ORDER BY Email "

You want just one [instance of each] Email address, so you can't display CustNo because there are several of them. So you have to choose which CustNo you want - MAX, MIN, ... AVERAGE?

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-11 : 00:53:02

i'd go with max(CustNo)/min(CustNo) in the hope that one customer has ID=0.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources