Advantage Database Server ORDER BY behaviour

Question

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.

Answer 1

Use aliases in your statements to make it clearer, and probably to solve the issue.

Rather than:

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

Use

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

The problem might be that it is confusing the field value in the order by clause -- it does not know which table to get it from, the 1st or 2nd.

Answer 2

I think you need to move the ORDER BY outside the subquery:

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

If you are wanting the rownum() to be applied to the ordered result set (I'm a bit slow this morning), then it might be necessary to use something like the following:

SELECT Name, Value, ROWNUM() FROM 
  (SELECT top 100 PERCENT * FROM MainTable WHERE Value > 0 order by value ) a

I don't think the ORDER BY in the subquery is allowed unless it actually changes the result ... however in this case, it does seem like it should be allowed.