The ROWNUM function returns the number representing the order of the records that will be generated by the query result. The first result record is assigned 1, and the second result record is assigned 2.
ROWNUM and INST_NUM() can be used in the SELECT statement, and GROUPBY_NUM() can be used in the SELECT statement with GROUP BY clauses. The ROWNUM function can be used to limit the number of result records of the query in several ways. For example, it can be used to search only the first 10 records or to return even or odd number records.
The ROWNUM function has a result value as an integer, and can be used wherever an expression is valid such as the SELECT or WHERE clause. However, it is not allowed to compare the result of the ROWNUM function with the attribute or the correlated subquery.
The following is an example (demodb) that returns country names ranked first to fourth based on the number of gold medals in the 1988 Olympics.
--Limiting 4 rows using ROWNUM in the WHERE condition
SELECT * FROM
(SELECT nation_code FROM participant WHERE host_year = 1988
ORDER BY gold DESC) AS T
WHERE ROWNUM <5;
--Limiting 4 rows using FOR ORDERBY_NUM()
SELECT ROWNUM, nation_code FROM participant WHERE host_year = 1988
ORDER BY gold DESC
FOR ORDERBY_NUM() < 5;
--Unexpected results : ROWNUM operated before ORDER BY
SELECT ROWNUM, nation_code FROM participant
WHERE host_year = 1988 AND ROWNUM < 5
ORDER BY gold DESC;