CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Pawan Nogariya
Pawan Nogariya

Reputation: 9000

Checking distinct values on column level

I have four columns coming from my query. My requirement is to check if the values of all the columns are different then only select the result.

I have written this query and it is working fine. But I was just wondering if there is any better or shortcut way to achieve this

select FO, AFO, CO, ACO from mytable 
where 
(fo<>afo or (fo is null or afo is null))
and 
(fo<>co or (fo is null or co is null))
and 
(fo<>aco or (fo is null or aco is null))
and 
(afo<>co or (afo is null or co is null)) 
and 
(afo<>aco or (afo is null or aco is null))
and 
(co<>aco or (co is null or aco is null))

Upvotes: 1

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Hmmm . . . you seem to want the four values to be different or NULL. A different method uses apply:

select t.*
from mytable t cross apply
     (select count(*)
      from (values (t.afo), (t.fo), (t.co), (t.aco)
           ) v(val)
      where val is not null
      having count(*) = count(distinct val)
     ) x;

This removes the NULL values and then checks that the remaining ones are all distinct.

Upvotes: 1

Related Questions