Rownum and the Top-N query

Rownum is a special column that can be used to limit the number of results returned by a query. However, not understanding exactly how it works can lead to unexpected results. Let’s go into some simple scenarios involving the rownum clause.

Here’s our table definition:
create table employee (empname varchar(20), empid number(10));

Let’s say we want to retrieve 5 rows from this table. This is what the query might look like.
select * from employee where rownum <= 5;

Now if we want the top 5 empids from this table, here’s a novice attempt.  This query will not return the top 5 empids as desired, but just some 5 rows sorted by empids.
select * from employee where rownum < 5 order by empid desc;

Here’s what we need to do. What follows is called a Top-N query. This query contains a nested query that retrieves the rows from this table sorted by the empids, and then applies a rownum clause on the results of this query to return the desired result.
select * from (select * from employee order by empid desc) where rownum <=5;

Essentially a Top-N query is a query used to retrieve the top N rows from a table, based on desired conditions. In our example, our query was attempting to retrieve the top 5 empids from the employee table.

(Please note that all queries listed here were run and tested on Oracle.)