Advantage Database Server ORDER BY behaviour
- by ie
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.
