generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.