6

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
sinandrei
  • 179
  • 2
  • 10
  • See my answer on exact same question http://stackoverflow.com/questions/33298291/running-total-that-changes-each-time-a-column-value-changes/33298565#33298565 – Giorgi Nakeuri Oct 27 '15 at 18:24

1 Answers1

4

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
Hogan
  • 69,564
  • 10
  • 76
  • 117
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • It would be helpful to explain how this works via creating a number that only changes when the partition changes. – Hogan Oct 27 '15 at 19:05
  • @Hogan, well it someting difficult to explain unless you degud the different parts of the statements yourself. It is standard inlands solution. – Giorgi Nakeuri Oct 27 '15 at 19:09
  • I understand you are to special to write it. OK, I will do it for you. – Hogan Oct 27 '15 at 19:14
  • See... not so hard to explain -- less than 100 words. – Hogan Oct 27 '15 at 19:21
  • @Hogan, thanks. I am now with a smartphone... but I really think that explanation is hard to understand if you have no practice here. I still think debugging is the way to go to understand it. By ghe way you have some errors there. `will get the same number for every row in the partition` this is not true... – Giorgi Nakeuri Oct 27 '15 at 19:24
  • I guess you are nit picking not having the word seqence there so I put it in, but please edit YOUR answer and make it better. – Hogan Oct 27 '15 at 19:32
  • For some reason I am finding it hard to figure out what makes this work, but yes, it does solve the problem. – Kinnison84 Apr 17 '18 at 11:26