Change Column Number

Question

I have NAME and PAY, but I need CHANGEGROUP in this example:

NAME PAY DATE CHANGEGROUP Sally 12 10/01/2011 1 Sally 12 10/01/2011 1 Sally 12 11/02/2011 1 Sally 12 11/02/2011 1 Sally 12 12/01/2012 1 Sally 13 04/23/2013 2 Sally 12 04/24/2013 3 Sally 10 05/01/2013 4 Sally 10 10/01/2014 4 

I tried RANK() and DENSE_RANK() , but they are grouped according to the value - because the payment goes down, it will ruin my grouping. I saw this one , but it is incompatible with this old version of SQL 2005

Answer 1

This is a problem of gaps and islands.

One approach. SQL Fiddle

 WITH T1 AS (SELECT *, ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY DATE) - ROW_NUMBER() OVER ( PARTITION BY NAME, [PAY] ORDER BY DATE) AS Grp FROM Table1), T2 AS (SELECT *, MIN(DATE) OVER ( PARTITION BY NAME, Grp) AS MinDate FROM T1) SELECT [NAME], [PAY], [DATE], DENSE_RANK() OVER ( PARTITION BY NAME ORDER BY MinDate) AS CHANGEGROUP FROM T2 ORDER BY NAME, MinDate