sql-serversql-server-2008partitioningdense-rank

Row_Number() partitioning according to consecutive rows


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

Solution

  • 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;