Manually specify starting value for Row_Number()

Question

I want to define the start of ROW_NUMBER() as 3258170 instead of 1.

I am using the following SQL query

SELECT ROW_NUMBER() over(order by (select 3258170))  as 'idd'. 

However, the above query is not working. When I say not working I mean its executing but its not starting from 3258170. Can somebody help me?

The reason I want to specify the row number is I am inserting Rows from one table to another. In the first Table the last record's row number is 3258169 and when I insert new records I want them to have the row number from 3258170.

Answer 1

Just add the value to the result of row_number():

select 3258170 - 1 + row_number() over (order by (select NULL)) as idd 

The order by clause of row_number() is specifying what column is used for the order by. By specifying a constant there, you are simply saying "everything has the same value for ordering purposes". It has nothing, nothing at all to do with the first value chosen.

To avoid confusion, I replaced the constant value with NULL. In SQL Server, I have observed that this assigns a sequential number without actually sorting the rows -- an observed performance advantage, but not one that I've seen documented, so we can't depend on it.

Answer 2

I feel this is easier

ROW_NUMBER() OVER(ORDER BY Field) - 1 AS FieldAlias (To start from 0) ROW_NUMBER() OVER(ORDER BY Field) + 3258169 AS FieldAlias (To start from 3258170)