The inspiration for this post came after recently overhearing a developer asking why his query was not returning a row. It had been working fine, the only change was instead of ROWNUM = 1, he had changed it to ROWNUM = 2.
The query was could have been doing something similar to the following:
SELECT x.* FROM (SELECT e.employee_id, e.last_name, e.salary FROM employees e ORDER BY salary DESC) x WHERE ROWNUM = 1 /
which returns information about the top earning employee.
However when the last line of the query is changed to ROWNUM = 2 to find the second highest earning employee no rows are returned and this is what was causing confusion.
I have read enough Tom Kyte to understand superficially why the change stopped the query from working but not in enough detail to stand up in front of my peers or even my cat for that matter and confidently explain why. This post is my attempt to change that.
What is ROWNUM? In Oracle terms, ROWNUM is a pseudocolumn which returns a number for each row returned by a query. The ROWNUM returned is not permanently assigned to a row. A ROWNUM value is assigned after it passes the predicate part of the query but before any sorting or aggregation. It is important to know that the ROWNUM is only incremented after it has been assigned.
Stepping through our example, where ROWNUM = 2. The first row fetched is assigned a ROWNUM of 1 and makes this condition false (So at this point ROWNUM = 1, we are looking for ROWNUM = 2 or to put it another way 1 = 2 which of course is false), the second row to be fetched is now the first row and ROWNUM is still 1 (It hasn’t incremented because it wasn’t assigned) so again the condition is false and this repeats for all rows so none are returned.
So how do you find the second highest earning employee? That will be the subject of a future post.