SQL Query to find the lowest/top N records on a column

Question: SQL Query to find the lowest/topmost N records based on a column?

Answer: Other popular variants of this question are:-

SQL Query to find the lowest/highest 5/10/N employees/table based on Salary/Any Other Column?

SQL Query to find the bottom/top 5/10/N employees/table based on Salary/Any other Column?

Finding the lowest/bottom N records

There are several ways depending upon which version of Oracle DB are you using. For 8i and above you can use an 'inner query' to get the result. Prior to that version we used to have the same achieved by using a 'sub query'. Oracle 9i and above makes such tasks even more simple by providing RANK() and DENSE_RANK() functions.

Let's take the approach where we use an 'inner query' to get the result:-

SELECT * FROM (SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME)WHERE ROWNUM < N;

Here the inner query 'SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME' find all the records of the given table and orders them in ascending order based on the COLUMN_NAME. Now the external query selects all the rows having the ROWNUM less than 'N' out of the sorted resultset. Since, the inner query is sorting the records in the ascending order hance the records selected with 'ROWNUM <>

Using RANK() (or similarly DENSE_RANK()) function:-

SELECT * FROM (SELECT COL1, COL2, ..., RANK() OVER (ORDER BY COL2) COL2_RANK FROM TABLE_NAME)WHERE COL2_RANK <= N;

In this case, the RANK() function is based on the column named 'COL2' and the external query selects the records having COL2_RANK as 1 ... N. Since, the default sort order for RANK() (or DENSE_RANK()) function is 'ascending' hence the above query will return the bottom N records of the table named 'TABLE_NAME' based on the column named 'COL2'.

How to find the highest/top N records

Well... now it should be quite easy to find them. In all the above approaches, we simply need to add the keyword 'DESC' in the ORDER BY clause to have the ordered sequence in the Descending Order. Everything else will remain the same.