SQL Puzzle | First row number for every partition should be odd 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 odd 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 RowNumber
101 a 1
102 b 3
102 c 4
103 e 5
103 f 6
103 q 7
104 r 9
105 p 11

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) + SUM(a) OVER (ORDER BY Id,vals) - a RowNumber FROM ( SELECT *, IIF(Id = LEAD(Id) OVER (ORDER BY Id,Vals), 0 , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Vals)%2)a FROM Row_Nums )k -- ```

Output-1

 ```-- /*------------------------ SELECT Id,Vals, ROW_Number() OVER (ORDER BY Id,Vals) + SUM(a) OVER (ORDER BY Id,vals) - a RowNumber FROM ( SELECT *, IIF(Id = LEAD(Id) OVER (ORDER BY Id,Vals), 0 , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Vals)%2)a FROM Row_Nums )k ------------------------*/ Id Vals RowNumber ----------- ---------- -------------------- 101 a 1 102 b 3 102 c 4 103 e 5 103 f 6 103 q 7 104 r 9 105 p 11 (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