Tags

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


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

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

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