Menu
I am working on a query for SQL Server 2005 that needs to return data with two 'index' fields. The first index 't_index' should increment every time the 'shade' column changes, whilst the second index increments within the partition of the values in the 'shade' column:
t_index s_index shade 1 1 A 1 2 A 1 3 A 1 4 A 1 5 A 2 1 B 2 2 B 2 3 B 2 4 B 2 5 B To get the s_index column I am using the following:
Select ROW_NUMBER() OVER(PARTITION BY [shade] ORDER BY [shade]) as s_index My question is how to get the first index to only increment when the value in the 'shade' column changes?
284
SQL Server Row_Number group by And based upon that partition, Row_Number() function will assign the integer values to each record starting from 1. And on the other side, the Group By statement in SQL Server is used to group rows that have the same values.
The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition. The following shows the syntax of the ROW_NUMBER() function: ROW_NUMBER() OVER ( [PARTITION BY partition_expression, ... ]
To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row# . You must move the ORDER BY clause up to the OVER clause. SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.
If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.
You can try to use DENSE_RANK() for that:
SELECT shade, s_index = ROW_NUMBER() OVER(PARTITION BY [shade] ORDER BY [shade]), t_index = DENSE_RANK() OVER (ORDER BY [shade]) FROM dbo.YourTableNameHEre Gives output:
shade s_index t_index A 1 1 A 2 1 A 3 1 A 4 1 A 5 1 B 1 2 B 2 2 B 3 2 B 4 2 B 5 2 That can be accomplished with the DENSE_RANK() function:
DENSE_RANK() OVER(Order By [shade]) as t_index 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
