Add ascending number (per ID) in SQL Server

Question

I have a table with articlenumbers and a number of specific codes.

Now I would like to add an ascending number to the articles, restarting on each new article.

For example my list is:

A 1234
A 4321
A 1233
B 1222
B 2222
C 1111
D 1258
D 1285

I would like to add the last number, like this:

A 1234 1
A 4321 2
A 1233 3
B 1222 1
B 2222 2
C 1111 1
D 1258 1
D 1285 2

It's a simple table, so the select for the first part is:

SELECT ART, CODE FROM tblArtCodes ORDER BY ART

I checked the following link - MySQL Add rownumbers per ID But couldn't get it to work :-(

Answer 1

If your mysql version is lower than 8 then you should use subquery as follows:

SELECT ART, CODE,
       (Select count(*) from tblArtCodes tt
         Where t.art = tt.art and t.code >= tt.code) as rn
 FROM tblArtCodes t ORDER BY ART, rn

If you are on mysql 8 or higher then you can use row_number as follows:

SELECT ART, CODE,
       Row_number() over (partition by art order by code) as rn
FROM tblArtCodes t ORDER BY ART, rn