ROW_NUMBER function does not start from 1

Question

I would like to ask about strange behaviour in SQL Server whilst using ROW_NUMBER() Function. Typically it should start from 1 and Order values by the selected column in Order By clause, which for the most scenarios works for me just as it is supposed to, but I have a particular case when I use a basic Select Statement:

SELECT 
    ROW_NUMBER() OVER (ORDER BY VIN) AS RN,
    * 
FROM dbo.RawData

and I get such result:

RN      VIN
6301    JTEBR3FJ00K096082
6302    JTEBR3FJ00K096132
6303    JTEBR3FJ00K096146
6304    JTEBR3FJ00K096163
6305    JTEBR3FJ00K096180
6306    JTEBR3FJ00K096275
1801    5TDDZRFHX0S820530
1802    5TDDZRFHX0S824111
1803    5TDDZRFHX0S824500
1804    5TDDZRFHX0S825971
1805    5TDDZRFHX0S826456

and those are the first columns in the return table. The whole ROW_NUMBER function works randomly, after chain from 6301 to 6306, the chain from 1801 to 1940 starts etc.

The VIN column (the one I sort data based on) is set to nvarchar(17)

could you please help with solving the issue which might occur in this case?

I would be grateful for any tips what might be wrong

Answer 1

You can use ORDER BY to order the rows in a desired way:

SELECT ROW_NUMBER() OVER (ORDER BY VIN) AS RN
      ,* 
FROM dbo.RawData
ORDER BY RN;

As the row_number is calculated in the SELECTE, you can use its value in the ORDER BY clause without the need of nested query.