I want to select 2 columns from a table, and assign a int value to each value. However, I want the 1st column ID to be the same for all values that are the same.
For the 2nd column, I want each value to numbered as well, but partitioned by the first column. I have figured this piece out, but I can't get the first part to work.
Here is the test scenario I'm using.
DECLARE @TestTable as Table (Column1 char(1), Column2 char(1))
INSERT INTO @TestTable SELECT 'A','A'
INSERT INTO @TestTable SELECT 'A','B'
INSERT INTO @TestTable SELECT 'A','C'
INSERT INTO @TestTable SELECT 'B','D'
INSERT INTO @TestTable SELECT 'B','E'
INSERT INTO @TestTable SELECT 'B','F'
INSERT INTO @TestTable SELECT 'B','G'
INSERT INTO @TestTable SELECT 'B','H'
INSERT INTO @TestTable SELECT 'C','A'
INSERT INTO @TestTable SELECT 'C','B'
INSERT INTO @TestTable SELECT 'C','C'
SELECT
Row_Number() OVER (Partition BY Column1 ORDER BY Column1) as Column1_ID,
Column1,
Row_Number() OVER (Partition BY Column1 ORDER BY Column1, Column2) as Column2_ID,
Column2
FROM @TestTable
When I run this, the values in Column2_ID are correct, but I would like the values for Column1_ID to be as follows.
Column1_ID Column1 Column2_ID Column2
1 A 1 A
1 A 2 B
1 A 3 C
2 B 1 D
2 B 2 E
2 B 3 F
2 B 4 G
2 B 5 H
3 C 1 A
3 C 2 B
3 C 3 C
First, you need to write a CTE in which you assign a number to each row within each group. To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
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'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you pass in any arguments to OVER , the numbering of rows will not be sorted according to any column.
You just need to use a different ranking function,
dense_rank() OVER (ORDER BY Column1) as Column1_ID
http://msdn.microsoft.com/en-us/library/ms173825.aspx
SQL Fiddle : http://www.sqlfiddle.com/#!6/d41d8/1832
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