Get MAX value of a BIT column

Question

I have a SELECT request with 'inner join' in the joined table is a column with bit type.

I want to select 1 if in the joined table is at most one value with 1. If it is not the case the value will be 0.

So If I have:

PERSID | NAME 1      |  Toto 2      |  Titi 3      |  Tata 

And the second table

PERSID | BOOL 1      |  0 1      |  0 2      |  0 2      |  1 

I would like to have for result

Toto -> 0 Titi -> 1 Tata -> 0 

I try this:

SELECT       sur.*     ,MAX(bo.BOOL)          FROM SURNAME sur                   INNER JOIN BOOL bo     ON bo.IDPERS = sur.IDPERS 

But MAX is not available on BIT column.. So how can I do that?

Thanks,

Answer 1

Try:

max(cast(bo.BOOL as int)) 

Answer 2

you can cast it to an INT, and even cast it back to a BIT if you need to

SELECT       sur.*     ,CAST(MAX(CAST(bo.BOOL as INT)) AS BIT)     FROM SURNAME sur                   INNER JOIN BOOL bo     ON bo.IDPERS = sur.IDPERS