How can I OR all the values in a particular column across multiple rows?

  • Thread starter Thread starter Ken Varn
  • Start date Start date
K

Ken Varn

Is there any way that I can run an ADO.NET query in SQLServer to do a
bitwise OR operation on a particular column for multiple rows?

i.e., Select OR(ColumnName) from Table

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------
 
Ken:

Here is a Where clause that I use to select multiple rows based on a mask
(int value) passed in:

WHERE (@pRecStatusMask & RecStatus)>0

So, if the mask has a value of 7, any rows with a RecStatus of 1,2,3,4,5,6
or 7 will get selected.

I believe this is what you needed?
 
Jon said:
Ken:

Here is a Where clause that I use to select multiple rows based on a mask
(int value) passed in:

WHERE (@pRecStatusMask & RecStatus)>0

So, if the mask has a value of 7, any rows with a RecStatus of 1,2,3,4,5,6
or 7 will get selected.

I believe this is what you needed?
Click to expand...

No I think he needs the value of 7 to be returned by the select if
there are 3 records, with value 1, 2 and 4. I toyed with the idea of
suggesting a query which uses an aggregate (SUM()) but I thought that
that isn't the answer, as the values might not be just flags but could
have more bits set, which then would result in bad values. :)

So I think it can't be done in 1 query, but perhaps some sql wiz knows
how to solve it :)

FB
:
Click to expand...


--
 
Yes, Frans you're right. I overlooked his example (i.e., Select
OR(ColumnName) from Table). However, I don't see the value of a function
like this, particularly on integer type columns. Assuming a column type of
tinyint that can have random values of 0 to 255 and some large number of
rows, this function will tend to always return 255.
I guess I'm still missing what Ken is asking.
 
Ken,

As long as the column you're checking is an integer, you can do the
following..

Select * From <Table> Where <ColumnName> | <MaskValue> = <MaskValue>

e.g. if you have 8 rows with numbers 1 thru 8, then a mask of 7 would return
rows 1 to 7, but not 8.

HTH
Adrian Parker
 
No I think he needs the value of 7 to be returned by the select if
there are 3 records, with value 1, 2 and 4. I toyed with the idea of
suggesting a query which uses an aggregate (SUM()) but I thought that
that isn't the answer, as the values might not be just flags but could
have more bits set, which then would result in bad values. :)

So I think it can't be done in 1 query, but perhaps some sql wiz knows
how to solve it :)
Click to expand...

Your are right on what I am looking for. I want an equivalent SUM operation
but doing a bitwise OR instead.

I was directed to this query that seems to do what I am looking for. Seems
like a lot of work. I wish there was just a simple keyword like SUM to do
it, but the results are what I want. Not sure how good this is when it
comes to performance.

SELECT SUM(Distinct Value & 1) + SUM(Distinct Value & 2) + SUM(Distinct
Value & 4) + SUM(Distinct Value & 8) -- Take this out to bits neeeed

FROM tbl


--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------
 
Back
Top