CODEWITHSUNDEEP
sqlt-sql
dMilan
dMilan

Reputation: 257

How to check If table contains diferent values?

I have table: 

Id Value
1  79868
2  79868
3  79868
4  97889
5  97889

Now, I want to make next select with bool variable that check if table contains difrent values at table column Value. Something like this:

 select  
        v= (select case when exists(...)
                    then 1 
                    else 0
                    end)

Table contais Values: 79868, 97889 so v should return 1 in other case 0. How to write select iniside select case??

Upvotes: 1

Views: 247

Answers (6)

paparazzo
paparazzo

Reputation: 45096

This is a the same as another answers
But is has some test data 

declare @T table(pk int identity primary key, val int not null);
insert into @T (val) values (79868), (79868), (79868);
select case when count(distinct(val)) = 1 then 0 else 1 end as dd 
  from @t t;
select case when min(val) = max(val) then 0 else 1 end as dd 
  from @t t;
insert into @T (val) values (97889), (97889);
select case when count(distinct(val)) = 1 then 0 else 1 end as dd 
  from @t t;
select case when min(val) = max(val) then 0 else 1 end as dd 
  from @t t;

I like the min max answer from Gordon best

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

So, you just need one case expression with two Boolean variable 

declare @bit1 bit = 1, @bit0 bit = 0

select 
      (case when min(value) = max(value) then @bit1 else @bit0 end) as v 
from table t
where value is not null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can compare the min and max values:

select (case when (select min(value) from t) = (select max(value) from t)
             then 1 else 0
        end) as all_same

With an index on (value), this should be quite fast.

The above solution assumes that there are no null values or that NULL values should be ignored.

Upvotes: 4

Robert Kock
Robert Kock

Reputation: 6018

May this is better:

SELECT CASE COUNT(DISTINCT value) WHEN 1 THEN 1
                                         ELSE 0
       END AS all_equal
FROM   my_table;

Upvotes: 0

Gaurav
Gaurav

Reputation: 1109

If I get your question correct, you want to check if value column contains more than 1 distinct values. You can achieve this using, 

select (case when count(value) > 1 then 1 else 0 end) as out 
from (select value from table group by value)  temp

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6018

You might try this:

SELECT CASE COUNT(*)
         WHEN 1 THEN 1
                ELSE 0
       END AS all_equal
FROM (SELECT DISTINCT Value FROM my_table);

Upvotes: 2

Related Questions