CODEWITHSUNDEEP
sqloracle-database
nabulke
nabulke

Reputation: 11255

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

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.

Upvotes: 4

Views: 15212

Answers (1)

psaraj12
psaraj12

Reputation: 5072

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

Upvotes: 4

Related Questions