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 RowFilterFROM Table1 hl ORDER BY RowFilter
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 RequiredResultfrom Table1 h1--Try this It Is worked for me...