RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Performing Top-N Queries in Oracle

Use a single SQL statement to perform a top-N query.

he task of retrieving the top or bottom N rows from a database table (by salary, sales amount, credit, etc.) is often referred to as a "top-N query." This task is fairly common in application development.

The most straightforward, but inefficient, way of accomplishing such a query is by retrieving all rows from the database table(s), sorting them by specified criteria, scanning from the top, and then selecting the top N rows. This is definitely not an elegant solution.

Writing a procedure that retrieves and sorts all rows from a database table requires lots of code, and it probably won't execute as quickly as you'd expect.

Use a single SQL statement to perform a top-N query. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK() and DENSE_RANK().

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date