I'm using ADS v10 beta. I'm trying to numerate ordered resultset.

1) ORDER BY in nested queries. I need to use nested SELECT for some calculations:

SELECT Name, Value, ROWNUM() FROM (SELECT * FROM MainTable WHERE Value > 0 ORDER BY Value) a

And I'm getting

Expected lexical element not found: )
There was a problem parsing the table
names after the FROM keyword in your
SELECT statement.

Everything is working well when the ORDER BY is removed. Although, I found the sample in the Help, it looks like my query (more complex, indeed):

SELECT * FROM (SELECT TOP 10 empid, fullname FROM branch1 ORDER BY empid) a UNION SELECT empid, fullname FROM branch2 ORDER BY empid

2) ORDER BY + ROWNUM(). I used the nested query in the example above, to numerate ordered rows. Is there are any chance to avoid nested query? In the SQL Server I can do something like this:

SELECT Name, Value, ROW_NUMBER() OVER(ORDER BY Value) FROM MainTable WHERE Value > 1 ORDER BY Value

Please advice. Thanks.