CODEWITHSUNDEEP
sqlsql-serverduplicatesaggregate-functions
Ram
Ram

Reputation: 99

Need to fetch only duplicate values and ignore the unique values in SQL server

Table name is SAMPLE and Columns are DID, RID and CAT

In the table we see DID column 100 has same CAT values like CA repeated twice and the DID 200 and 500 also have duplicate values in CAT column. so i want in the output like below

100 CA
200 CA
500 OA

300 and 400 DID do not have any duplicate values are all unique so in my query these records should be excluded

Please help on the query part.

table information:

enter image description here

Upvotes: 1

Views: 518

Answers (1)

Popeye
Popeye

Reputation: 35920

You can use group by and having as follows:

select did, cat
  from your_table
group by did, cat
having count(1) > 1

You can also use the count window function as follows:

select did, cat from
(select t.*, count(1) over (partition by did, cat) as cnt
from your_table t) t
where cnt > 1

Upvotes: 0

Related Questions