Generate row index for table based on last updated date

user_13733081 0
Viewed 39

I would like to generate an index row in a table that may contain duplicates. the index need to be based on values from the table and last update date.

The data looks like this:

ID    Val1  LastUpdateDate 
--   ------  -------------
1      0     07.09.2019
1      2.5   12.09.2019
1      2.5   27.09.2019
1      3.5   01.10.2019
1      2.5   24.10.2019
1      0     01.11.2019

I would like to have:

  ID    Val1  LastUpdateDate    index
    --   ------  -------------   ----
    1      0     07.09.2019      1
    1      2.5   12.09.2019      2
    1      2.5   27.09.2019      2
    1      3.5   01.10.2019      3
    1      2.5   24.10.2019      4
    1      0     01.11.2019      5

I've tried with the following code but it's not working:

  SELECT ID
         ,Value1
         ,Value2
         ,Value3
         ,LastUpdateDate
         ,(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY last_update_date) - ROW_NUMBER()OVER(PARTITION BY  ID,Value1,Value2,Value3 ORDER BY ID,Value1,Value2,Value3)) AS index
FROM Table1
ORDER BY LastUpdateDate
sql-server sql
1 Answers

You can interpret this as a gaps-and-islands problem. However, I think the simplest way is to use LAG() and count the changes:

You seem to want dense_rank():

SELECT t1.*,
       SUM(CASE WHEN prev_val1 = val1 THEN 0 ELSE 1 END) OVER (PARTITION BY id ORDER BY last_update_date) as seqnum
FROM (SELECT t1.*,
             LAG(val1) OVER (PARTITION BY ID ORDER BY last_update_date) as prev_val1
      FROM Table1 t1
     ) t1
ORDER BY LastUpdateDate;

Note that index is a really bad name for a column, because it is a SQL keyword.

user_11440350
Related