SQL Puzzle | First row number for every partition should be Even number Puzzle – 1

In this puzzle you need to generate row numbers for the given data. The condition is that the first row number for every partition should be even number.For more details please check the sample input and expected output.

Sample Input

Id Vals
101 a
102 b
102 c
103 f
103 e
103 q
104 r
105 p

Expected Output

Id Vals Changed
101 a 2
102 b 4
102 c 5
103 e 7
103 f 8
103 q 9
104 r 11
105 p 13

Script

Use below script to create table and insert sample data into it.

 ```-- /* Data Generation CREATE TABLE Row_Nums ( Id INT ,Vals VARCHAR(10) ) GO INSERT INTO Row_Nums VALUES (101,'a'), (102,'b'), (102,'c'), (103,'f'), (103,'e'), (103,'q'), (104,'r'), (105,'p') GO */ -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- Select Id,Vals, ROW_Number() OVER (ORDER BY Id,Vals)+1 + SUM(a) OVER (ORDER BY Id,vals) - a Changed FROM ( SELECT *, IIF(Id = LEAD(Id) OVER (ORDER BY Id,Vals), 0 , (ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Vals)*2 +1) % 2 )a FROM Row_Nums )k -- ```

Output-1

 ```-- /*------------------------ Select Id,Vals, ROW_Number() OVER (ORDER BY Id,Vals)+1 + SUM(a) OVER (ORDER BY Id,vals) - a Changed FROM ( SELECT *, IIF(Id = LEAD(Id) OVER (ORDER BY Id,Vals), 0 , (ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Vals)*2 +1) % 2 )a FROM Row_Nums )k ------------------------*/ Id Vals Changed ----------- ---------- -------------------- 101 a 2 102 b 4 102 c 5 103 e 7 103 f 8 103 q 9 104 r 11 105 p 13 (8 row(s) affected) -- ```

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com