How to return a incremental group number per group in SQL

Question

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:

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.

Solution 1

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

Solution 2

I think you are looking for this:

ROW_NUMBER() OVER (PARTITION BY hl.id ORDER BY hl.ts_DateTime) AS RowFilter

Solution 3

-- Here Is my answer Mr.Chris Ballance :

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,
   DENSE_RANK() OVER (PARTITION BY hl.ts_datetime ORDER BY hl.Tagname) AS RequiredResult
from Table1 h1

--Try this It Is worked for me...

Comments

[user1080381]

I've edited John Woo's answer. It does not enumerate RowFilter properly. Check this fiddle sqlfiddle.com/#!18/1e62d/15

[mtholen]

Hello and welcome to SO! If you are posting something akin to 'code' you may want to try and use the curly braces sign in the editor window? That will make it much clearer what SQL statement you suggested?