CODEWITHSUNDEEP
sqlsql-server-2008
BarendB
BarendB

Reputation: 287

Best practice to apply bitwise AND logic on SELECT

I'm trying to find the best way to apply the following logic. Our business logic requires me to insert a bunch of rows into a var table, then select all distinct records but set a condition to positive only if it matches all records with same id [So basically bitwise AND]

Hopefully my example explains that better.

DECLARE @sometable TABLE (
record_id INT NOT NULL,
some_condition BIT NOT NULL)

records in that table would match

record_id some_condition
1         1
1         0
2         0
3         0

In the above case the desired output should be

record_id some_condition
1         0
2         0
3         0

Till now how I've been doing this is like this

SELECT DISTINCT record_id  CAST(MIN(CAST(some_condition INT)) AS some_condition
FROM @sometable
GROUP BY record_id

Is there a better way to do this? I'm having to CAST the var as MIN only takes numeric types.

Upvotes: 0

Views: 308

Answers (2)

PinnyM
PinnyM

Reputation: 35533

Firstly, you can leave out DISTINCT, since GROUP BY already makes them distinct. Also, you can avoid the innermost CAST if you just add 0:

SELECT record_id,  CAST(MIN(some_condition+0) as BIT) AS some_condition
FROM @sometable
GROUP BY record_id

Finally, do you really need to recast to a BIT when done? Depending on how the query results are used, that might not be needed.

Upvotes: 1

Beth
Beth

Reputation: 9607

how about using a case statement?

select record_id,
min(case when some_condition=0 then 0 else 1 end) as minCond
from sometable
group by 
record_id

Upvotes: 1

Related Questions