Menu
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.
374
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
We can use the group by multiple column technique to group multiple records into a single record. All the records that have the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple column technique.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
you shouldn't be using ROW_NUMBER(),
DENSE_RANK() insteadPARTITION 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
