oracle SQL语句中结合order by子句使用rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10430762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using rownum in oracle SQL statement in combination with order by clause
提问by nabulke
Which of the following two SQL statements will return the desired result set(i.e. the ten rows with Status=0 and the highest StartTimes)?
以下哪两条 SQL 语句将返回所需的结果集（即 Status=0 且 StartTimes 最高的十行）？
Will both statements always return the same result set(StartTime is unique)?
两个语句是否总是返回相同的结果集（StartTime 是唯一的）？
SELECT *
FROM MyTable
WHERE Status=0
AND ROWNUM <= 10
ORDER BY StartTime DESC
SELECT *
FROM (
SELECT *
FROM MyTable
WHERE Status=0
ORDER BY StartTime DESC
)
WHERE ROWNUM <= 10
Background
背景
My DBAdmin told me that the first statement will firstlimit the table to 10 rows and than order those random rows by StartTime, which is definitly not what I want. From what I learned in that answer, the order by clause of the second statement is redundant and could be removed by an optimizer, which is also not what I want.
我的 DBAdmin 告诉我，第一个语句将首先将表限制为 10 行，然后按 StartTime 对这些随机行进行排序，这绝对不是我想要的。从我在那个答案中学到的，第二条语句的 order by 子句是多余的，可以被优化器删除，这也不是我想要的。
I asked a similar questionconcering the limitclause in a query to a SQLite database and am interested in understanding any differences to the above statements (using limitVs rownum) used with an oracle db.
我问了一个关于SQLite 数据库查询中的子句的类似问题，limit并且我有兴趣了解与 oracle db 一起使用的上述语句（使用limitVs rownum）的任何差异。
采纳答案by psaraj12
Your Second Query will work
您的第二个查询将起作用
Because in the first ,the first ten rows with Status 0 are selected and then the order by is done in that case the first ten rows fetched need not be in the highest order
因为在第一个中，选择状态为 0 的前十行，然后按顺序完成，在这种情况下，获取的前十行不需要按最高顺序排列
