Search code examples
sql-serverrow

Row number for for same value

The result of my SQL Server query returns 3 columns.

Select Id, InItemId, Qty 
from Mytable 
order by InItemId

result

I need to add a column, call it row, that starts from 1 and will increase by 1, based on the initemid column with same value.

So the result should be:

result2

Thank you !

Solution

  • Use row_number():

    select row_number() over (partition by initemid order by initemid) as row,
       t.*
from t;

    Note: There is no ordering within a given value of initemid. SQL tables represent unordered sets and there is no obvious column to use for ordering.