select - Advantage 数据库服务器 ORDER BY 行为

Question

我正在使用 ADS v10 测试版。我正在尝试计算有序的结果集。

1) 嵌套查询中的 ORDER BY。我需要使用嵌套的 SELECT 进行一些计算:

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

我得到了

Expected lexical element not found: )There was a problem parsing the tablenames after the FROM keyword in yourSELECT 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()。我在上面的示例中使用了嵌套查询来计算有序行。有没有机会避免嵌套查询？在 SQL Server 中，我可以这样做:

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

请指教。谢谢。

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 是允许的，除非它实际上改变了结果......但是在这种情况下，它似乎应该被允许。