SQLITE - Test if all rows returned from a SELECT have the same value for a particular column-CodePudding

I have the following table

id    || foo     
-------------
1     ||   a    
2     ||   b   
3     ||   b  
4     ||   b
5     ||   c
6     ||   a
7     ||   d

Given a list of IDs, how can I test whether all of those records have the same foo value?

For example

selecting ids in (2,3,4) should pass the test

selecting ids in (2,3) should pass the test

selecting ids in (1,6) should pass the test

selecting ids in (5,6) should fail the test

selecting ids in (1,2) should fail the test

(Note that I don't immediately have the "foo" value to test against, so for example with (2,3,4) I don't know to test against "b", but there's nothing stopping me from using the first id=2 to get the "b" value to test against)

CodePudding user response：

If the column foo is not nullable you can do it with aggregation:

SELECT MIN(foo) = MAX(foo) AS result
FROM tablename
WHERE id IN (?, ?, ....);

or:

SELECT COUNT(DISTINCT foo) = 1 AS result
FROM tablename
WHERE id IN (?, ?, ....);

Both queries will return 1 row with 1 column and a value of 1 will indicate that all rows of the resultset (after the table is filtered) contain the same value in the column foo.

See the demo.