How to return a incremental group number per group in SQL

To anyone who can help,

I would like create a data query in SQL to incrementally number groups of rows, grouped on a common datetime and keep the “group numbers” incrementing on the next datetime and so on. These “group numbers” must not reset for each group as I have seen when using the partition by statement. Here is my sample data:

  • Can't connect to Windows Azure Database
  • sql server 2008: is it possible to specify format instead of using formatfile?
  • SQL Server tempdb optimization tips for a new server?
  • SQL Current month/ year question
  • T-SQL BETWEEN problem max value first
  • SQL Server how to set a default value when the column is null
  • ts_DateTime          |ID   |Value|RowFilter|RequiredResult
    --------------------------
    2013/01/09 09:23:16  |8009 |0    |1        |1
    2013/01/09 09:23:16  |8010 |0    |2        |1
    2013/01/09 09:23:16  |8026 |0    |3        |1
    
    2013/01/09 09:23:22  |8026 |0    |1        |2
    
    2013/01/09 09:23:28  |8009 |0    |1        |3
    2013/01/09 09:23:28  |8010 |0    |2        |3
    2013/01/09 09:23:28  |8026 |0    |3        |3
    
    2013/01/09 09:27:03  |8009 |0    |1        |4
    2013/01/09 09:27:03  |8010 |0    |2        |4
    2013/01/09 09:27:03  |8026 |0    |3        |4
    
    2013/01/09 09:27:09  |8009 |0    |1        |5
    2013/01/09 09:27:09  |8010 |0    |2        |5
    2013/01/09 09:27:09  |8026 |0    |3        |5
    
    2013/01/09 09:27:15  |8009 |0    |1        |6
    2013/01/09 09:27:15  |8010 |0    |2        |6
    2013/01/09 09:27:15  |8026 |0    |3        |6
    
    
    

    The query I am using to get these results is :

    select hl.ts_DateTime,  hl.Tagname as [ID],  hl.TagValue as [Value],
    ROW_NUMBER() OVER (PARTITION BY hl.ts_datetime ORDER BY hl.tagname) AS RowFilter
    from Table1 hl
    

    So basically, looking at the RowFilter column, I am getting a unique ROW number per ts_DateTime partition. What I actually need is that for each ts_DateTime partition the RowFilter column should look like the Required result column.

    One Solution collect form web for “How to return a incremental group number per group in SQL”

    you shouldn’t be using ROW_NUMBER(),

    • use DENSE_RANK() instead
    • remove PARTITION BY

    query,

    SELECT hl.ts_DateTime,  
           hl.Tagname as [ID],  
           hl.TagValue as [Value],
           DENSE_RANK() OVER (ORDER BY ts_datetime) AS RowFilter
    FROM   Table1 hl 
    ORDER  BY RowFilter
    
    • SQLFiddle Demo
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.