SQL: MAX like implementation of OR on Grouped object

Asked Mar 05 '20 at 05:14

Active Mar 05 '20 at 05:59

Viewed 14 times

I have a table Example like this:

Id, Type, Flags(in integer)
1,   A,    8 (B1000)
2,   A,    7 (B0111)
3,   B,    3 (B0011)

The Flags are stored as integer, but interpreted as Binary. When I group by Type to get the MAX of each Type, I do it like this:

SELECT Type, MAX(Flags)
FROM Example
GROUP BY Type

What can I do to get the Logical OR of all the values in a Group of Flags? eg: what I want is this:

Type, ResultFlags
A,    15 (B1111 = B1000 | B0111)
B,    3 (B0011)

Is there a way to do this in a GROUP BY clause or any other way which isn't too costly?

I am also curious how you can do this in LINQ to SQL.

edited Mar 05 '20 at 05:59

asked Mar 05 '20 at 05:14

Omkar

do you want sum of Flags filed(8+7=15)? – Tushar Mar 05 '20 at 05:17
`SELECT Type, BIT_OR(Flags) FROM Example GROUP BY Type` – Nick Mar 05 '20 at 05:27
@Tushar not the sum, but the logical OR B1010 | B1101 should give me B1111, not B10111 – Omkar Mar 05 '20 at 05:52
@Nick I need it for sql server. `BIT_OR` works for mysql only. – Omkar Mar 05 '20 at 05:59
1

@Omkar https://stackoverflow.com/questions/3981050/aggregate-bitwise-or-in-a-subquery – Nick Mar 05 '20 at 06:03

0 Answers0