Auto increment column in select query

agrawal.meet · #1 (**permalink**) 01-18-11, 02:08

Hello Experts,

This question has forever eluded me. So posting here.

How can we generate a unique number for every row returned.

for example:

Code:

Select id = identity(10), Name, Address
from Table1

Above query gives you syntax error saying you can only generate id if you are actually inserting into a table and not in the select statement.

I know this is possible in Oracle but is this even possible in Sybase ASE 12.5?

Hope the question is clear.

Thanks,
Meet

pdreyer · #2 (**permalink**) 01-18-11, 03:09

No, you'll have to insert into a temp table then select from the temp table

Code:

Select id = identity(10), Name, Address
into #t1
from Table1
select * from #t1
drop table #t1

agrawal.meet · #3 (**permalink**) 01-18-11, 03:15

That's exactly my question. This means that this is not possible in sybase ASE? You have to insert into a temp table first?

pdreyer · #4 (**permalink**) 01-19-11, 02:35

Yes, you have to insert into a temp table first.
If your table isn't large and you have a unique key then you can generate it with a self join.
Provide some sample data if you want an example.

Internet Services

Web Development

Digital Marketing

Consumer Tech