在Oracle SQL语句中结合使用rownum和order by子句 [英] Using rownum in oracle SQL statement in combination with order by clause

问题描述

以下两个SQL语句中的哪个将返回所需的结果集(即，状态为= 0且启动时间最高的十行)?

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)?

两个语句是否总是返回相同的结果集(StartTime是唯一的)吗?

Will both statements always return the same result set (StartTime is unique)?

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

背景

我的DBAdmin告诉我，第一条语句 first 将表限制为10行，然后按StartTime排序那些随机行，这显然不是我想要的. 从我在该答案中学到的知识中，第二条语句的order by子句是多余的，可以由优化器删除，这也不是我想要的.

My DBAdmin told me that the first statement will first limit 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.

我问了一个类似的问题，将查询中的limit子句赋予了SQLite数据库，并且有兴趣了解与上述语句(使用limit与rownum)在Oracle数据库中的使用存在任何差异.

I asked a similar question concering the limit clause in a query to a SQLite database and am interested in understanding any differences to the above statements (using limit Vs rownum) used with an oracle db.

推荐答案

您的第二个查询将可用

因为在第一行中，选择了状态为0的前十行，然后按顺序进行排序，在这种情况下，提取的前十行不一定是最高顺序

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

这篇关于在Oracle SQL语句中结合使用rownum和order by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！