CODEWITHSUNDEEP
sqlsequence
Trexion Kameha
Trexion Kameha

Reputation: 3580

Sequence generator as column in a select statement in SQL

I'm trying to do a select statement or insert statement into another table where I add a sequence to the first column.

Please see below:

create table test (
nm      varchar(20),
age         decimal(38,12));

insert into test values ('Albert',20),('Ben',10),('Charles',30)

I am trying to run a query where when I do:

select 
##some kind of sequence function## as seq,
nm,
age
from test

I get back:

seq nm age
1 Albert 20
2 Ben 10
3 Charles 30

Also, I'd like to be able to set the starting number to the sequence, so I can set it at 50, and the sequence will show 51,52, 53, etc.

Thank you

Upvotes: 0

Views: 259

Answers (2)

mmelendez
mmelendez

Reputation: 1

You can use IDENTITY:

CREATE TABLE test
(
  id INT IDENTITY (50,1),
  nm VARCHAR(20) ,
  age DECIMAL(38, 12)
);

INSERT  INTO test
VALUES  ( 'Albert', 20 ),
        ( 'Ben', 10 ),
        ( 'Charles', 30 )

That will give you:

id   nm       age
---------------------------
50   Albert   20.000000000000
51   Ben      10.000000000000
52   Charles  30.000000000000

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

    select 50+row_number() over (order by nm) as rnum,
    nm,age from test

Upvotes: 1

Related Questions