75

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.

Chris Ballance
  • 33,810
  • 26
  • 104
  • 151
DLR
  • 796
  • 1
  • 6
  • 12
  • 1
    What kind of SQL? This sort of thing is database-dependent. –  Jan 16 '13 at 13:44
  • I was stuck with the same problem and just couldn't see the solution. The key here is to take the `PARTITION BY` clause of the first window function and make it the `ORDER BY` clause of the second window function (which is unpartitioned). – sayap Apr 02 '13 at 07:41
  • 4
    I find it ironic that the "wrong result" you're getting is actually what I need. :) Thanks for posting the code. – Gellie Ann May 21 '15 at 09:52
  • Me too - You're 'wrong result' just solved my problem - Thanks and upvoted! – Mark G Nov 22 '17 at 16:14

3 Answers3

132

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    I've edited John Woo's answer. It does not enumerate RowFilter properly. Check this fiddle http://sqlfiddle.com/#!18/1e62d/15 – user1080381 Mar 09 '18 at 16:31
10

I think you are looking for this:

ROW_NUMBER() OVER (PARTITION BY hl.id ORDER BY hl.ts_DateTime) AS RowFilter
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
1

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

Thrishank Creations
  • 11
  • 3
  • 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? – mtholen Apr 11 '21 at 14:52