Using ORDER BY Software

Question

I am using ADS v10 beta. I am trying to count an ordered set of results.

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 get

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

Everything works well when ORDER BY is deleted. Although, I found a sample in the Help, it looks like my request (more complex, really):

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 subquery in the example above to number the ordered lines. Is it likely to avoid nested queries? In 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

I ask for advice. Thank.

Answer 1

, ORDER BY :

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

, rownum() ( ), - :

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

, ORDER BY , ... , .

Answer 2

, , , .

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

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

, value order by - , , 1 2.