Using rownum in oracle SQL statement in combination with order by clause

Question

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

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

Background

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.

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.

The 1st query doesn't even parse. You may want to edit your question. — Phil, May 03 '12 at 11:54
Sorry for the errors. I fixed and verified statement one against our database. The second statement should be syntactically correct. — nabulke, May 03 '12 at 12:08
Related: [How to select the nth row in a SQL database table?](http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table) — ypercubeᵀᴹ, May 03 '12 at 12:16
Also: [Wikipedia: FETCH FIRST clause](http://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause) — ypercubeᵀᴹ, May 03 '12 at 12:17
For Oracle: [ROW_NUMBER vs ROWNUM](http://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/) — ypercubeᵀᴹ, May 03 '12 at 12:21
The order by clause will not be removed by the Oracle optimiser in these circumstances — David Aldridge, May 03 '12 at 15:05

score 4 · Accepted Answer · answered May 03 '12 at 12:27

4

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

answered May 03 '12 at 12:27

psaraj12

4,772
2
21
30

Using rownum in oracle SQL statement in combination with order by clause

1 Answers1

Linked