CODEWITHSUNDEEP
sqlsql-serverbitwise-operators
Kyle O'Donnell
Kyle O'Donnell

Reputation: 61

SQL - bitwise AND all results of a boolean value field

I have a table that looks like this (just a single Boolean field):

| IsTrue      |
---------------
| 1           |
---------------
| 1           |
---------------
| 0           |
---------------
| 1           |
---------------

Is there a way I can do a Bitwise AND on all values in this field so that I have a single True/False result?

Something like: select BITWISE_AND(IsTrue) from MyTable which would have a result of just | 0 | ?

Upvotes: 1

Views: 567

Answers (2)

Mark Adelsberger
Mark Adelsberger

Reputation: 45659

Probably the most generic solution, if I can take at face value that the column contains either a 1 or a 0 as you've shown it above - is min(IsTrue).

But a couple caveats:

You're asking for a "bitwise" AND, but you're describing a "logical" AND - which, if the column holds a numeric "1=TRUE or 0=FALSE", is the same; but in other cases may be only kinda-sorta the same.

Some databases provide bitwise operators, others don't. And a bitwise aggregate function is something else entirely. This is why min is a more general solution; it's a standard function that any database will provide. And it will work in a wide range of circumstances, including the one you seem to be outlining. It would fail if the column type is technically one over which your DBMS doesn't define min (or defines it in some weird way). It would fail if a negative number could be interpreted as "true" (but in that case, maybe you could get by with min(abs(isTrue)) or something like that).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you have a value that only takes on 0 or 1, then "bitwise and" is equivalent to min():

select min(IsTrue) as "bitwise_and"

And "bitwise or" is equivalent to max():

select max(IsTrue) as "bitwise_or"

This is a numerical fact, so it is independent of the database being used.

Upvotes: 4

Related Questions