James123
James123

Reputation: 11652

how to generate Serial numbers +Add 1 in select statement

I know we can generate row_number in select statement. But row_number starts from 1, I need to generate from 2 and onwards.

example

party_code
----------
R06048
R06600
R06791
(3 row(s) affected)
I want it like

party_code serial number
---------- -------------
R06048       2
R06600       3
R06791       4 

Current I am using below select statement for generate regular row number.

 SELECT party_code, ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

How can modify above select statement and start from 2?

Upvotes: 7

Views: 96946

Answers (2)

Yahia
Yahia

Reputation: 70369

I don't know much about SQL Server but either one of these will work:

SELECT party_code, 1 + ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

OR

SELECT party_code, serial_numer + 1 AS [serial number] FROM
(SELECT party_code, ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable)
ORDER BY party_code

Upvotes: 2

AakashM
AakashM

Reputation: 63338

SELECT party_code, 1 + ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

to add: ROW_NUMBER() has an unusual syntax, and can be confusing with the various OVER and PARTITION BY clauses, but when all is said and done it is still just a function with a numeric return value, and that return value can be manipulated in the same way as any other number.

Upvotes: 19

Related Questions