Get MAX value of a BIT column
SqlSql ServerTsqlSql Server-2008Stored ProceduresSql Problem Overview
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,
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
Try:
max(cast(bo.BOOL as int))
Solution 3 - Sql
One way
SELECT
sur.*
,MAX(convert(tinyint,bo.BOOL))
FROM SURNAME sur
INNER JOIN BOOL bo
ON bo.IDPERS = sur.IDPERS
Solution 4 - Sql
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
Solution 5 - Sql
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