CODEWITHSUNDEEP
sqlsql-server-2008selectdistinct
BobSki
BobSki

Reputation: 1552

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 RecNo however, filtering by distinct ClientNo, 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?

EDIT:

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

Upvotes: 0

Views: 70

Answers (4)

Serg
Serg

Reputation: 22811

Choose only ClientNo having the max Recno (or replace < with > to choose the min one).

Select *
from TblA t1
where not exists(select 1 
         from TblA t2
         where t1.ClientNo = t2.ClientNo and t1.Recno < t2.Recno )

BTW, the other solution already mentioned, utilizing row_number() needs no CTE in this case

SELECT TOP(1) WITH TIES *
FROM TblA
ORDER BY ROW_NUMBER() OVER(PARTITION BY ClientNo ORDER BY Recno)

Upvotes: 1

Matt
Matt

Reputation: 14381

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
FROM
    TblA
GROUP BY
    ClientNo

Upvotes: 2

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try this..

   ;with cte1
  As(SELECT Recno,clientno 
  ,row_number() over(partition by clientno order by Recno )RNO FROM TblA)
   Select Recno,clientno 
   From cre1 where RNO=1

Upvotes: 1

Lamak
Lamak

Reputation: 70678

Ok, this will give you the count that you want:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY ClientNo ORDER BY Recno)
    FROM TblA
)
SELECT COUNT(DISTINCT Recno) N
FROM CTE
WHERE RN = 1;

Upvotes: 1

Related Questions