Lot of times you have tried to use ROWNUM clause along along with the ORDER BY clause and must have been treated with a different output than you expected. You would expect the ROWNUM to be applied after the results have been ordered by the column, but instead it applies the ROWNUM and then does an order by.
Lets look at an example:
Support you had the following data in a student table:
And we want to find out the youngest student, you would immediately write:
SELECT * FROM student WHERE ROWNUM=1 ORDER BY age;
which would give the output:
But looking at the data above John/David would should have been the youngest. As I said before, the ORDER BY clause is applied after the ROWNUM selects one row. Lets edit the query a bit and try:
SELECT * FROM (SELECT * FROM student ORDER BY age) WHERE ROWNUM=1;
which would give us the correct output:
The idea here is to first order the rows/records by moving the order by query as a nested sql and then applying ROWNUM on the resultant data.