I was desperately trying to find this issue here, but could not find anything related to this question.
In SQL (SMSS), I want to have an ID column. Values for the ID should be generated based on 3 columns in the same table.
ID | column1 | column2 | column3
1 | 2016 | 101 | 1
2 | 2017 | 101 | 1
2 | 2017 | 101 | 1
3 | 2017 | 303 | 1
Can anyone help me to have this ID generated?
First, please note that having duplicate rows in a database table is not a good idea. Rows in a database table should be unique.
However, this doesn't mean that your table only contains these columns - it might very well contain other columns that are simply irrelevant to your question, so here goes:
Use DENSE_RANK()
.
First, Create and populate sample table (Please save us this step in your future questions)
DECLARE @T AS TABLE
(
column1 int,
column2 int,
column3 int
)
INSERT INTO @T (column1, column2, column3) VALUES
(2016, 101, 1),
(2017, 101, 1),
(2017, 101, 1),
(2017, 303, 1)
The query:
SELECT DENSE_RANK() OVER(ORDER BY Column1, Column2, Column3) AS ID,
Column1,
Column2,
Column3
FROM @T
Results:
ID Column1 Column2 Column3
1 2016 101 1
2 2017 101 1
2 2017 101 1
3 2017 303 1
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