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.
| Author |
Topic |
|
nicklasj86
Starting Member
3 Posts |
 Posted - 2011-08-17 : 07:40:21
|
| I have a table with all the registered orders and a column with the customer_id. But, I want to only show unique rows(only one customer_id). If a customer place 2 orders than I will have duplicates. How do I only retrieve unique rows so I only get one cust_id?select COUNT(distinct strCustomerID) as 'All customers'from ReportSalesImport.dbo.tblOrderRegistered206000 rows should I get but I get over 300 000 rows when i run the select query with distinct.//Nicklas |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-17 : 07:44:00
|
| even if you've multiple records per customer count (distinct customerid) should only count them as 1 so you should get distinct count of customers correctly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-17 : 07:52:44
|
Why do you think 206000 is the wrong count?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-08-17 : 08:26:42
|
quote:
206000 rows should I get but I get over 300 000 rows when i run the select query with distinct.
You should get ONE row with a result-value for 'All customers'.If there are over 300000 rows then you have used another statement than the posted one.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nicklasj86
Starting Member
3 Posts |
Posted - 2011-08-17 : 11:36:36
|
| I solved this by using RANK() function in a view and delete every values that have > 1 .I could also use distinct(customer_id). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-18 : 04:40:19
|
| sorry didnt get why you got wrong count. was the strCustomerID repeating?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
How to retrieve only unique rows?