Praveen D'Souza via SQLMonster.com (fo***@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp