CODEWITHSUNDEEP
sqlsql-servert-sqluniqueaggregate
p.campbell
p.campbell

Reputation: 100567

TSQL: finding unique entries in a single table

Consider a table or CTE structured like this:

Name    Num
----    ----
Abc     12
Abc     12
XYZ     70
XYZ     80
XYZ     85
Bar     50
Bar     55
Foo     44
Foo     44
Baz     88

The requirement is to determine the Name where multiple different Nums exist.

The desired resultset is

Name   
----
XYZ     
Bar

What TSQL statement would you use to derive this resultset?

Update: indeed there could be 2+ entries for a given Name.

Upvotes: 4

Views: 181

Answers (1)

gbn
gbn

Reputation: 432260

Assumes no NULLs in Num column

select
   Name
from
   MySetObject
group by
   name
having
   min(num) <> max(num)
    -- also COUNT(DISTINCT Num) > 1 achieves the same

Upvotes: 9

Related Questions