Dense_Rank() Related to Island Gap T-SQL Query


Question is from MSDN:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b6589664-e351-4d6d-afd4-13309395cd7e/denserank-is-ordering-by-the-partition-column-and-i-dont-want-it-to?forum=transactsql

I have used two ways to solve the problem. One is for SQL Server 2012 or above and the other is for SQL Server 2005 or above.

 
 create table dataSet (Fruit varchar(50), EatenDate Datetime)
Insert into dataset 
SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate
UNION ALL
SELECT 'Apple', '20130102'
UNION ALL
SELECT 'Pear', '20130102'
UNION ALL
SELECT 'Pear', '20130103'
UNION ALL
SELECT 'Apple', '20130104'
UNION ALL
SELECT 'Apple', '20130105'
UNION ALL
SELECT 'Pear', '20130106'
UNION ALL
SELECT 'Apple', '20130107'
 
 --Query for your question
;With mycte1 AS(
 
SELECT Fruit, EatenDate
,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit Order by EatenDate,Fruit DESC) rnDelta
FROM dataSet
)  
,mycte2 as (
Select EatenDate,Fruit 
 ,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit, rnDelta Order by rnDelta, EatenDate, Fruit DESC) rnDelta2
 FROM mycte1
)
SELECT Fruit, EatenDate, DENSE_RANK()  OVER(ORDER BY rnDelta2) AS Ranking 
 FROM  mycte2

 
Order by EatenDate,Fruit
/*
Fruit	EatenDate	Ranking
Apple	2013-01-01	1
Apple	2013-01-02	1
Pear	2013-01-02	2
Pear	2013-01-03	2
Apple	2013-01-04	3
Apple	2013-01-05	3
Pear	2013-01-06	4
Apple	2013-01-07	5
*/

 --Query for your question If you are using SQL Server 2012

;With mycte AS(
 
SELECT Fruit,EatenDate
, CASE WHEN Fruit =lag(Fruit,1)Over(Order by eatendate,FRuit) Then 0 Else 1 END l1

FROM dataSet
)  

Select EatenDate,Fruit ,Sum(l1) Over(Order By EatenDate,Fruit) Ranking
 FROM  mycte 
Order by EatenDate,Fruit

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s