Bobski Bobski - 1 year ago 110
SQL Question

SQL Server query distinct

I'm trying to do a query in SQL Server 2008. This is the structure of my table

Recno ClientNo ServiceNo
1234 17 27
2345 19 34
3456 20 33
4567 17 34

I'm trying to select
however, filtering by distinct
, so for some clients such as client no 17 - they have more than 1 entry, I'm trying to count that client only once. So basically, looking at this table, I'm only supposed to see 3 RecNo's, since there are only 3 distinct clients. Please help

Select RecNo, Count(ClientNo)
from TblA
where Count(clientNo)<2

Something like this?


The value of
is not relevant, I only need to have an accurate number of records. In this case, I'd like to have 3 records.

Answer Source

oaky you are getting some crazy answers probably becuase your desired result is not clear so I suggest if some of these are not what you need that you clarify your desired result.

If you want the answer 3, I can only assume you want a count of DISTINCT ClientNo's if so it is simply aggregation.

SELECT COUNT(DISTINCT ClientNo) as ClientNoDistinctCount
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download