SQL Bitwise comparison for different groups in one column

Question

I would like to obtain result of bitwise AND comparison for the next table

gr value
1 300
1 340
1 150
2 520
2 460

In this example I want to see comparison between 300, 340 and 150 (group 1) and between 520 and 460 (group 2). The problem is that the number of elements in each group can be any.

There is no built-in function. You might find this instructive: http://stackoverflow.com/questions/3981050/aggregate-bitwise-or-in-a-subquery. — Gordon Linoff, Mar 22 '14 at 23:25
I don't understand in that example why AND operation gives result for OR. As far as i understand 1, 2, 3 and 4 in binary don't have any common ones, so AND operation for all of the sum should give 0. But OR instead gives 111 which is 7 in dec. — niydiyin, Mar 23 '14 at 00:03

score 0 · Answer 1 · answered Mar 23 '14 at 01:07

You can do what you want this way:

select gr,
       (min(value & 1) + min(value & 2) + min(value & 4) + min(value & 8) + min(value & 16) +
        min(value & 32) + min(value & 64) + min(value & 128) + min(value & 256) & min(value & 512)
       ) as bit_and
from table t
group by gr;

You might need to extend the summations to higher bit values.

Leax · Answer 2 · 2017-02-16T11:36:56.760

Here's example (thanks to Vyacheslav Davydenko):

-- example for aggregate bitwise operations
SET NOCOUNT ON

declare @t table (gr int, mask tinyint)

INSERT INTO @t(gr, mask) VALUES
 (1, 1) -- 00000001
,(1, 3) -- 00000011
,(1, 5) -- 00000101
,(2, 9) -- 00001001
,(2, 8) -- 00001000

select 'source',* from @t

declare @maskSize tinyint = 8*(select top 1 DataLength(mask) from @t) -- bits count

-- bitwise AND
;with Bits as
(
    select BitMask=cast(1 as bigint)
    union all
    select 2 * BitMask from Bits where BitMask < POWER(cast(2 as bigint), @maskSize)
)
, ResultBit AS
(
    select gr, res=sum(BitMask)
    from (
        select gr, BitMask, cnt=count(BitMask), sm=sum(res)
        from (
            select 
                t.gr
                ,b.BitMask
                ,res=iif(t.mask & b.BitMask=0, 0, 1)
            from @t t
            cross join Bits b
        )z
        group by gr, BitMask
        having count(BitMask)=sum(res)
    )z
    group by gr
)
select example='AND',* from ResultBit
order by gr

-- bitwise OR
;with Bits as
(
    select BitMask=cast(1 as bigint)
    union all
    select 2 * BitMask from Bits where BitMask < POWER(cast(2 as bigint), @maskSize)
)
, ResultBit AS
(
    select 
        gr
        ,res=SUM(DISTINCT iif(t.mask & b.BitMask=0, 0, b.BitMask)) -- 
    from @t t
    cross join Bits b
    group by gr
)
select example='OR',* from ResultBit

In result you'll get:

;gr;mask
source;1;1
source;1;3
source;1;5
source;2;9
source;2;8

example;gr;res
AND;1;1
AND;2;8

example;gr;res
OR;1;7
OR;2;9

SQL Bitwise comparison for different groups in one column

2 Answers2