Tags

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


SQL Puzzle | The Infamous ROW_NUMBER Puzzle | Advanced SQL

In this puzzle you have to ignore the countinues duplicate values and and get the row number for the non duplicate values. For more details please see the sample input and expected output.

Sample Input

Id City
1 Pune
2 Nasik
3 Pune
4 Pune
5 Nasik
6 Nasik

Expected Output

City Rank
Pune 1
Nasik 2
Pune 3
Nasik 4

Script – DDL and INSERT Sample Data

--

CREATE TABLE Cities
(
	 Id INT
	,City VARCHAR(50)
)
GO

INSERT INTO Cities VALUES 
 (1,'Pune')
,(2,'Nasik')
,(3,'Pune')
,(4,'Pune')
,(5,'Nasik')
,(6,'Nasik')
GO

SELECT * FROM Cities
GO

--

SOLUTION – 1

--

SELECT City, x1 rank from 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY x1 order by Id) rnk from 
	(
		SELECT *, SUM(P) OVER(ORDER BY Id)+1 x1 FROM 
		(
			SELECT * , CASE WHEN City =  ISNULL(LAG(City) OVER (ORDER BY Id),City) THEN 0 ELSE 1 END p	
			FROM Cities 
		)x
	)k
)p WHERE rnk  = 1	 

--

Output – 1

--

City                                               rank
-------------------------------------------------- -----------
Pune                                               1
Nasik                                              2
Pune                                               3
Nasik                                              4

(4 rows affected)

--

SOLUTION – 1

--

SELECT x.City , ROW_NUMBER() OVER (Order by Id) Rank
FROM
(
	SELECT * , LAG(City) OVER (ORDER BY Id) PrevCity
	FROM Cities 
)x WHERE x.PrevCity IS NULL OR X.City != PrevCity	 

--

Output – 2

--

City                                               Rank
-------------------------------------------------- --------------------
Pune                                               1
Nasik                                              2
Pune                                               3
Nasik                                              4

(4 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com