CODEWITHSUNDEEP
sql
gilhanan
gilhanan

Reputation: 433

Check if value in column for all rows is exactly value

For example, I want follow the number 2 as target

This should return positive indication:

ID       Status
123      2
432      2
531      2
123      2

This should return negative indication:

ID       Status
123      1
432      3
531      2
123      2

This should return negative indication:

ID       Status
123      1
432      1
531      1
123      1

Thanks

Upvotes: 14

Views: 54502

Answers (3)

Tim
Tim

Reputation: 5421

select (select count(distinct status) from T)  = 1

will return 1 or 0 (i.e. true or false) depending on whether all of the rows have the same value in Status or not. If you have to deal with NULL values in status:

select exists 
( select status from T where status <> 2 or status is null)
as StatusContainsOtherThanTwoOrNullValue

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453837

EXISTS should be used in preference to COUNT so it can return as soon as the first non matching row is found.

SELECT CASE
         WHEN NOT EXISTS(SELECT *
                         FROM   your_table
                         WHERE  status <> 2) THEN 'Y'
         ELSE 'N'
       END AS your_result

You don't state RDBMS. You might need to append FROM DUAL onto the end of the above dependant on flavour.

Upvotes: 19

RedGrittyBrick
RedGrittyBrick

Reputation: 4002

select count(*) where Status != 2

Upvotes: 5

Related Questions