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.

0_0 I'm not sure that such a thing is possible, but I **am** sure that it's a horrible idea. What are you actually trying to accomplish? SQL Server has auto-incrementing integer columns, does it not? — , Mar 04 '13 at 20:03
can't you add an auto-incrementing primary key to keep the correct ID? — Matt Busche, Mar 04 '13 at 20:04
Yes, I can do that. Just trying to see if there is any other way. — Huzaifa, Mar 04 '13 at 20:11

Gordon Linoff · Accepted Answer · 2013-03-04T20:15:15.593

92

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.

edited Mar 04 '13 at 20:15

answered Mar 04 '13 at 20:05

Gordon Linoff

1,242,037
58
646
786

@John - you need to understand what ROW_NUMBER() is and that you cannot assign values to it. You can only add to it. From Oracle documentation: "ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1." – Art Mar 04 '13 at 20:48
For MySQL see OMG Ponies answer at: http://stackoverflow.com/questions/1895110/row-number-in-mysql – Oliver Zendel Aug 21 '14 at 12:21

score 11 · Answer 2 · answered Aug 19 '16 at 09:28

11

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)

answered Aug 19 '16 at 09:28

Tom McDonough

1,176
15
18

score 1 · Answer 3 · answered Dec 27 '17 at 21:07

Sometimes....

The ROW_NUMBER() may not be the best solution especially when there could be duplicate records in the underlying data set (for JOIN queries etc.). This may result in more rows returned than expected. You may consider creating a SEQUENCE which can be in some cases considered a cleaner solution. i.e.:

CREATE SEQUENCE myRowNumberId  
    START WITH 1  
    INCREMENT BY 1 
GO  

SELECT NEXT VALUE FOR myRowNumberId  AS 'idd' -- your query
GO

DROP SEQUENCE myRowNumberId; -- just to clean-up after ourselves
GO

The downside is that sequences may be difficult to use in complex queries with DISTINCT, WINDOW functions etc. See the complete sequence documentation here.

score 0 · Answer 4 · answered Feb 07 '20 at 12:02

I had a situation where I was importing a hierarchical structure into an application where a seq number had to be unique within each hierarchical level and start at 110 (for ease of subsequent manual insertion). The data beforehand looked like this...

Level Prod        Type  Component      Quantity     Seq
1   P00210005       R   NZ1500         57.90000000  120
1   P00210005       C   P00210005M     1.00000000   120
2   P00210005M      R   M/C Operation   20.00000000 110
2   P00210005M      C   P00210006      1.00000000   110
2   P00210005M      C   P00210007      1.00000000   110

I wanted the row_number() function to generate the new sequence numbers but adding 10 and then multiplying by 10 wasn't achievable as expected. To force the sequence of arithmetic functions you have to enclose the entire row_number(), and partition clause in brackets. You can only perform simple addition and substraction on the row_number() as such.

So, my solution for this problem was

,10*(10+row_number() over (partition by Level order by Type desc, [Seq] asc)) [NewSeq]

Note the position of the brackets to allow the multiplication to occur after the addition.

Level Prod        Type  Component      Quantity     [Seq] [NewSeq]
1   P00210005       R   NZ1500        57.90000000   120   110
1   P00210005       C   P00210005M    1.00000000    120   120
2   P00210005M      R   M/C Operation 20.00000000   110   110
2   P00210005M      C   P00210006     1.00000000    110   120
2   P00210005M      C   P00210007     1.00000000    110   130

Hema latha · Answer 5 · 2022-07-01T13:30:53.167

ROW_NUMBER() OVER(ORDER BY Field) - 1 AS FieldAlias (To start from 0) ROW_NUMBER() OVER(ORDER BY Field) - 2862718 AS FieldAlias (To start from 2862718)

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.

Manually specify starting value for Row_Number()

5 Answers5