Android: Knowing when all items in a database have their field set to the same value?

Question

I have an app that lets the user select every item in the list and set it as their "favorite". I thought it would be neat if, when all the items are set as favorite, the button switched to "unselect all".

The way I'm thinking about doing this is by registering a ContentObserver, which whenever a change was made checked if all the items are now set as favorite. Then I realized that may not be very efficient. I can't think of any other way, though, so any hints?

How can I know that all the items in the database have one of their fields set to the same value (field favorite = 1, in this case)?

Answer 1

Here is a solution that should (I believe) "abort fast" if a difference is found.

It avoids the sorting/distinct phase as with select distinct or group by. This approach may very well be slower in some situations and/or only very negligibly faster in others. SQLite is very darn fast! Also remember that indexes play a good role in RDMBS performance.

The inner select could be replaced with a literal value which would reduce the complexity. In any case, for amusement.

select exists
  (select * from t
   where val != (select val from t limit 1))

Answer 2

You could also do

SELECT FieldName FROM Table GROUP BY FieldName

If your result set has more than 1 row, they're not identical

Answer 3

A variant to the answer by Basiclife: SELECT DISTINCT FieldName FROM Table