Yoo Matsuo Yoo Matsuo - 6 months ago 9
SQL Question

How to generate a custom sequential number with SQL Server 2012

Is there any way to generate a custom sequential number like the following?
I want the Number to be incremented with grouping by the Code and Year.

Code Year Number
A 2016 1
A 2016 2
A 2016 3
B 2016 1
B 2016 2
C 2016 1
A 2017 1
A 2017 2


Any suggestion would be appreciated.

EDIT

Sorry, I was too ambiguous what I want. I want to generate the unique number when I query, so if I ask a new number in the above data context with Code:A and Year:2017, I want the Number to be 3. I guess to get the Number properly in a future I need to save the Code and Year with the Number.

Answer

Use ROW_NUMBER to assign Number per Code,Year grouping.

SELECT *,
    Number = ROW_NUMBER() OVER(PARTITION BY Code, [Year] ORDER BY (SELECT NULL))
FROM tbl

Replace SELECT NULL with the column you want the order to be based from.