T-SQL Query | [ The Bitwise AND Puzzle ]

Puzzle Statement

1. We have a table named BitwiseAnd.
2. Here we have Id and Value columns, now iwe have group by column Id and Bitwise AND all the values in that group Id.
3. For Id = 1 there are three values so we have calculate the Bitwise And like 10 & 1 & 13 ; Output for this is 2
4. For details please check out the sample input and expected output

Sample Input

Expected Output

Rules/Restrictions

• Your solution should be should use “SELECT” statement or “CTE”
• Your solution should be generic in nature.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE BitwiseAnd 
(
	 Id INT
	,Value INT
)
GO

INSERT INTO BitwiseAnd 
VALUES 
(1,10),
(1,3),
(1,14),
(3,13),
(4,1),
(4,12)

Update May 14 | Solution

--

/************   SOLUTION 1         ****************/



;WITH CTE AS
(
   SELECT * , ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY %%Physloc%% ) rnk FROM BitwiseAnd
)
,CTE1 AS
(
   SELECT Id , Value final , rnk FROM CTE WHERE rnk = 1
   UNION ALL
   SELECT c2.Id , c2.final & c1.Value final , c2.rnk + 1 FROM CTE1 c2 INNER JOIN CTE c1 ON c1.Id = c2.Id AND c1.rnk = c2.rnk 
)
SELECT x.Id , Final Bitwise
FROM CTE1 x
WHERE rnk = (SELECT MAX(rnk) FROM CTE1 y WHERE x.Id = y.Id )
ORDER BY Id




--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com