SQL Puzzle | The First Rank Puzzle

Puzzle Statement

Well this is good puzzle. One of my friend came to me with this scenario. Here you have first find how many unique partitions you have for first date. In our example first date – 6/1/2015 have 2 rows as their hedge values are different. Now we have 2 partitions for the first case. So for all the remaining cases we should return 2 rows. In this case for 7/1/2015 we should 2 rows even if there hedge are same.

Please go through the sample input and expected output for details.

Sample Input

6/1/2015	0
6/1/2015	100
7/1/2015	100
7/1/2015	100
7/1/2015	100

Expected Output

6/1/2015	0
6/1/2015	100
7/1/2015	100
7/1/2015	100

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

ScriptUse below script to create table and insert sample data into it.

--

CREATE TABLE RnkPerfect
(
	 Dt DATETIME
	,Hedge INT
)
GO

INSERT INTO RnkPerfect
VALUES
('6/1/2015',	0),
('6/1/2015',	100),
('7/1/2015',	100),
('7/1/2015',	100)
GO

INSERT INTO RnkPerfect
VALUES
('7/1/2015',	100)
GO

SELECT * FROM RnkPerfect
GO


--

SOLUTION 1 | Using Ranking Functions

--

;WITH CTE AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY dt ORDER BY dt,Hedge) RowNum 		
	FROM RnkPerfect
)
SELECT * FROM CTE WHERE RowNum <= (
SELECT MAX(RowNum) r FROM(
SELECT MIN(Dt) dt FROM CTE) a 
INNER JOIN CTE b ON a.dt = b.Dt ) 

--

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

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