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,

Outface

It looks like you need to use SUM and GROUP BY if you want to ensure that exactly one value is 1. – Colossus

Answer 1

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

Latia

Answer 2

Try:

max(cast(bo.BOOL as int))

E

He's just casting it as an integer so he can use the max aggregate function. – Cispadane

This one is much better. It also works with OVER clause. – Septicemia

Answer 3

You can avoid the messy looking double cast by forcing an implicit cast:

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

Wondrous

To me, this is messier, but +1 for showing a new way of doing it I haven't seen before – Yentai

Answer 4

One way

SELECT 
     sur.*
    ,MAX(convert(tinyint,bo.BOOL))    

    FROM SURNAME sur              
    INNER JOIN BOOL bo
    ON bo.IDPERS = sur.IDPERS

Invalidism

No they are the same, I just like convert better – Invalidism

Answer 5

If you want only those people with exactly one set bit:

declare @Surname as Table ( PersId Int, Name VarChar(10) )
insert into @Surname ( PersId, Name ) values
  ( 1, 'Toto' ), ( 2, 'Titi' ), ( 3, 'Tata' ), ( 4, 'Tutu' )

declare @Bool as Table ( PersId Int, Bool Bit )
insert into @Bool ( PersId, Bool ) values
  ( 1, 0 ), ( 1, 0 ),
  ( 2, 0 ), ( 2, 1 ),
  ( 4, 1 ), ( 4, 0 ), ( 4, 1 )

select Sur.PersId, Sur.Name, Sum( Cast( Bo.Bool as Int ) ) as [Sum],
  case Sum( Cast( Bo.Bool as Int ) )
    when 1 then 1
    else 0
    end as [Only One]
  from @Surname as Sur left outer join
    @Bool as Bo on Bo.PersId = Sur.PersId
  group by Sur.PersId, Sur.Name
  order by Sur.Name

Colossus

Answer 6

SELECT if(COUNT(id) > 0, 1, 0) FROM Books WHERE is_available = b'1';

Used b'1' for comparing boolean/bit in MariaDB, You can write accordingly as per your database.

Appal