Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements