How to return a incremental group number per group in SQL

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.

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

I think you are looking for this:

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

-- 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...