CODEWITHSUNDEEP
sqloracle-databaseselectsequence
André
André

Reputation: 25554

It is possible to do a autonumber sequence in a SELECT on Oracle?

I need to do a task in Oracle that I don't know how can I possible do this.

Ok, I need to do a SELECT when I define a autonumber sequence on-the-fly.

For example:

Select autonumber(1, 9000) as auto from some_table

And the result would be

auto
------
1
2
3
4
5
6
7
8
9
10
...
9000

This would be possible to do? Are there any oracle build in function that will help me doing this?

Upvotes: 4

Views: 19107

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

If you want a sequence of numbers independent of rows in an actual table, rather than numbering the returned rows (in which case look at rownum or row_number()), you can do:

select level as auto
from dual
connect by level <= 9000;

Upvotes: 20

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

You can use Oracle's built in rownum

select rownum as auto, other1, other2 from some_table

For ANSI compliance, you can use ROW_NUMBER() for later versions of Oracle

Upvotes: 1

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40499

select 
  rownum
from 
  dba_objects, 
  dba_objects
where
  rownum <= 9000;

Upvotes: -2

Related Questions