CODEWITHSUNDEEP
sqlsql-serverrow-number
Huzaifa
Huzaifa

Reputation: 1171

Manually specify starting value for Row_Number()

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.

Upvotes: 44

Views: 128244

Answers (4)

Tom McDonough
Tom McDonough

Reputation: 1262

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)

Upvotes: 14

Chloe Williams
Chloe Williams

Reputation: 21

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

Upvotes: 0

Milan
Milan

Reputation: 3335

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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.

Upvotes: 99

Related Questions