Generate ID based on multiple columns

Question

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?

Answer 1

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