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 🙂

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 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/