What is a good way to assign order #s to ordered rows in a table in Sybase
- by DVK
I have a table T (structure below) which initially contains all-NULL values in an integer order column:
col1 varchar(30),
col2 varchar(30),
order int NULL
I also have a way to order the "colN" columns, e.g.
SELECT * FROM T ORDER BY some_expression_involving_col1_and_col2
What's the best way to assign - IN SQL - numeric order values 1-N to the order table, so that the order values match the order of rows returned by the above ORDER BY?
In other words, I would like a single query (Sybase SQL syntax so no Oracle's rowcount) which assigns order values so that SELECT * FROM T ORDER BY order returns 100% same order of rows as the query above.
The query does NOT necessarily need to update the table T in place, I'm OK with creating a copy of the table T2 if that'll make the query simpler.
NOTE1: A solution must be real query or a set of queries, not involving a loop or a cursor.
NOTE2: Assume that the data is uniquely orderable according to the order by above - no need to worry about situation when 2 rows can be assigned the same order at random.
NOTE3: I would prefer a generic solution, but if you wish a specific example of ordering expression, let's say:
SELECT * FROM T
ORDER BY CASE WHEN col1="" THEN "AAAAAA" ELSE col1 END, ISNULL(col2, "ZZZ")
