Check if value in column for all rows is exactly value

Question

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

Do you want to know if all the values are the same or if they are all exactly 2 (for example)? — BlueMonkMN, Mar 27 '11 at 12:19
What is the data type of the status column? And what is the database? — Black Frog, Mar 27 '11 at 15:20

Martin Smith · Answer 1 · 2011-03-27T12:44:38.063

18

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.

edited Mar 27 '11 at 12:44

answered Mar 27 '11 at 12:16

Martin Smith

438,706
87
741
845

`SELECT *` can be changed to something like `SELECT 1` as improvement – demo Apr 09 '19 at 12:12
4

@demo that isn't an improvement. https://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists – Martin Smith Apr 09 '19 at 12:14
Ah, I didn't know that. Thx – demo Apr 09 '19 at 12:16

score 5 · Answer 2 · answered Mar 27 '11 at 12:16

5

select count(*) where Status != 2

answered Mar 27 '11 at 12:16

RedGrittyBrick

3,827
1
30
51

score 4 · Answer 3 · edited Apr 09 '19 at 12:09

4

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

edited Apr 09 '19 at 12:09

Tatranskymedved

4,194
3
21
47

answered Mar 27 '11 at 12:28

Tim

5,371
3
32
41

Check if value in column for all rows is exactly value

3 Answers3

Linked