# Dense_Rank() Related to Island Gap T-SQL Query

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'

;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

```