This clause is used to display the first N number of records of the query. SQL does not support TOP clause. But it uses ROWNUM.
Write a query to retrieve the first 5 records from BOOK_COPY tables which are received in 2014. Let us first write a query without ROWNUM.
SELECT * FROM BOOK_COPY WHERE TO_CHAR (RECEIVED_DATE, 'YYYY') = '2014';
SELECT * FROM BOOK_COPY WHERE TO_CHAR (RECEIVED_DATE, 'YYYY') = '2014';
Now we can see what ROWNUM clause has done to the result. It has displayed the first 5 records from the result set. It does not sort the results, but it simply picks the first 5 records from the result set.
Consider the below query.
SELECT * FROM BOOKS WHERE ROWNUM = 3;
These queries did not fetch any records from the table. Why? ROWNUM is a pseudo column and is no way related to DB, table or any other DB objects. It gets value when query is executed. That means, as query executes and gets its resultant records, ROWNUM value is incremented by one. Hence when we say ROWNUM< 5 in the query, it checks for ROWNUM values till it satisfies the condition and displays 4 records. But when we write ROWNUM = 3 and execute query, then it executes the select query, gets first record of the query, then ROWNUM becomes 1 and checks ROWNUM = 3 ? 1=3 which is wrong and discards first row, retrieves second row of the query, since first row is discarded ROWNUM us still 1 and check 1=3, which again makes row to be discarded. This process goes on till all the records are searched and all records of the query are discarded. Hence no results are displayed. Same check happens when ROWNUM > 3 is used i.e.; query checks for 1>3 for every record, and discards the records. Hence we do not get any records when we query with >, >= or = operator on ROWNUM. Only <= and < operators can be used with ROWNUM.