'DISTINCT' returns unexpected data

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' returns unexpected data

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-30 : 05:13:24

Hi, I am trying to return the customers that are unique in my table using this query

SELECT distinct custnum, billFname, BillLName, BillPhone, BillZip FROM Mytable

I am getting 40 additional records when I run this query, than when I run

SELECT COUNT DISTINCT(custnum) FROM Mytable

Upon further investigation I have found this is because it is treating the fields where the are slight differences, eg zipcode ad65ty and ad6 5ty as unique records (thus the customer appears twice in the returned results), any ideas on how I could structure my query to return records on distinct custnum only? (ignoring the rest of the fields eg zipcode, BillLname in the distinct part) so where there are differences like the one highlighted above the customer is only returned once!

Many thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 07:23:34

select custnum, billFname, BillLName, BillPhone, BillZip
from (select custnum, billFname, BillLName, BillPhone, BillZip, seq = row_number() over (partition by custnum) FROM Mytable) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 15:52:54

quote:
Originally posted by nigelrivett

select custnum, billFname, BillLName, BillPhone, BillZip
from (select custnum, billFname, BillLName, BillPhone, BillZip, seq = row_number() over (partition by custnum order by (select null)) FROM Mytable) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

it should have an order by part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources