SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 'DISTINCT' returns unexpected data		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

soulchyld21
Starting Member

United Kingdom
28 Posts
Posted - 05/30/2012 :  05:13:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 05/30/2012 :  07:23:34  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47960 Posts
Posted - 05/30/2012 :  15:52:54  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000