How to generate a custom sequential number with SQL Server 2012

Question

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.

Do you want to have this number in the table, or when you query the data? — krivtom
– krivtom, Commented May 27, 2016 at 5:21
Oh I forgot to mention that the generated number must be unique, so I guess the numbers have to be saved in the table — Yoo Matsuo
– Yoo Matsuo, Commented May 27, 2016 at 5:27

Felix Pamittan · Accepted Answer · 2016-05-27 05:22:59Z

2

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.

answered May 27, 2016 at 5:22

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yoo Matsuo Over a year ago

Oh such a quick reply. Thanks a lot! I guess all I need to do is save that number for a future inquiry.

Collectives™ on Stack Overflow

How to generate a custom sequential number with SQL Server 2012

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related