Removing duplicate rows - 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

SQL Server Development (2000)

Removing duplicate rows

Author

Topic

abhishekmadas
Starting Member

19 Posts

Posted - 2007-02-16 : 12:36:48

I have this query and I want rows with unique email addresses but what I get is distinct rows not distinct email addresses.

select distinct emailaddress,custid,customeracctnumber
from ServiceNumbersActivePackage
join tbluser
on accountnumber=customeracctnumber
where templateid = '2'
and emailaddress is not null

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 12:49:56

Lets assume that there are two rows with same Email address, for example


emailaddress custid customeracctnumber 
foo@bar.com  000001 CAN123
foo@bar.com  000002 CAN456

which of the two sets of values for [custid] and [customeracctnumber] do you want with your Email address?

You can't ask for a DISTINCT emailaddress without resolving which of the multiple values for the other columns you want. You could, for example, take the MINIMUM value of each ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources