SQL Puzzle | Odd and Even Row Numbers Puzzle

In this puzzle you need to generate odd and row numbers for the given data.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 OddRank EvenRank
101 a 1 2
102 b 3 4
102 c 5 6
103 e 7 8
103 f 9 10
103 q 11 12
104 r 13 14
105 p 15 16

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 * FROM ( SELECT *,ROW_NUMBER() OVER (ORDER BY Id,Vals) * 2 - 1 OddRank ,ROW_NUMBER() OVER (ORDER BY Id,Vals) * 2 EvenRank FROM Row_Nums )k -- ```

Output-1

 ```-- /*------------------------ SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (ORDER BY Id,Vals) * 2 - 1 OddRank ,ROW_NUMBER() OVER (ORDER BY Id,Vals) * 2 EvenRank FROM Row_Nums )k ------------------------*/ Id Vals OddRank EvenRank ----------- ---------- -------------------- -------------------- 101 a 1 2 102 b 3 4 102 c 5 6 103 e 7 8 103 f 9 10 103 q 11 12 104 r 13 14 105 p 15 16 (8 row(s) affected) -- ```

I really suspect the use of this but in case you need you can use

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