How can I count number of appearance of a value and make it into a new column when querying? [closed]

I have a table like this

ProductID | SalesOrderNumber

1 | SO0001

2 | SO0002

3 | SO0001

4 | SO0001

5 | SO0002

I want to query the table into this

ProductID | SalesOrderNumber | SalesOrderLineNumber

1 | SO0001 | 1

2 | SO0002 | 1

3 | SO0001 | 2

4 | SO0001 | 3

5 | SO0002 | 2

Basically, the SalesOrderLineNumber will count the number of time SalesOrderNumber appear. Everytime a same value of SalesOrderNumber show up, SalesOrderLineNumber increase.

How can I do it?

Answer

You should use:

SELECT ProductID,
       SalesOrderNumber,
       ROW_NUMBER() OVER (PARTITION BY SalesOrderNumber ORDER BY ProductId) as SalesOrderLineNumber
FROM yourTable;

The difference between this and the other answer is the use of ProductId in the ORDER BY clause. You seem to want the line numbers ordered by product id, so this seems to capture the intention.

More importantly: If the order has no duplicate products, then this always produces the same results — that is, the results are stable. If you repeat the partitioning key, then you could get different results each time you run the query.