I am working on a query for SQL Server 2008 that needs partition in a way that it considers the consecutive nature of the rows in the table, meaning that it has no "memory" and restart the row numbering when consecutiveness breaks down for a partition.
To illustrate:
declare @test table
(
CustomerId varchar(10),
ItemId varchar(10),
PlatformName varchar(10),
date datetime
)
insert into @test values ('aaaa', 'x', 'mobile','2015-10-24 22:52:47')
insert into @test values ('aaaa', 'x', 'mobile','2015-10-23 22:56:47')
insert into @test values ('aaaa', 'k', 'mobile','2015-10-22 21:52:47')
insert into @test values ('aaaa', 'k', 'tablet','2015-10-20 22:12:47')
insert into @test values ('aaaa', 'x', 'mobile','2015-10-19 20:52:47')
insert into @test values ('aaaa', 'k', 'tablet','2015-10-18 12:52:47')
insert into @test values ('aaaa', 'k', 'tablet','2015-10-16 12:52:47')
SELECT
t.*,
ROW_NUMBER() OVER (PARTITION BY t.CustomerId,t.ItemId,t.PlatformName ORDER BY t.Date DESC) as rowNo
FROM @test t
ORDER BY t.Date DESC
The following query returns:rowNo
1
2
1
1
3
2
3
Instead of the desired:
1
2
1
1
1
1
2
In case of Row 5 and 6 it should restart the counting because it is a new partition when you consider the consecutiveness breaks it apart from the initial partition.
I would also need to rank my rows in accordance with row numbering, as follows:
1
1
2
3
4
5
6
7
7
What you want to do is create an indicator that only changes when the partition changes. You can do this with the following trick. Since row number increments within a given partition, if you subtract that from an incrementing number within every row you will get the same number for the whole partition sequence.
Here is a chart at the start of any partition.
row number partition row number row number-partition number
x 1 x-1
x+1 2 x-1
...
x+n n+1 x-1
x will change at the next partition but partition number will start at 1 and you will get the same number for every row in the partition until the next sequential partition.
You then use this result as part of your partition and your problem is solved.
Here is how to code this in SQL:
WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER(
ORDER BY date DESC
) -
ROW_NUMBER() OVER(
PARTITION BY customerid, itemid, platformname
ORDER BY date DESC
) rn
FROM @test
)
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY customerid, itemid, platformname, rn
ORDER BY date DESC
) rn2
FROM
cte
ORDER BY
date DESC;