Skip to Main Content

go to Indiana University Indiana University


How To Retrieve Top (n) Rows from a Database

In SQL:Data Retrieval, one of the topics we cover is how to write a database query to answer questions like, “How many students were enrolled each term in Biology 101 from 1997-2008″ or “What is the average salary for the employees who work in the Marketing department?” These types of questions can be answered by using SQL functions. A function is a small bit of code that can accept a value, do something with it (like a calculation), and then return a new result.  In SQL, there are functions like AVG (to average) and COUNT (for, um, counting.  I bet you guessed that, though, right? :-)

Some functions, like AVG and COUNT, are available in almost every database product. Other functions are database-specific, made available by the database vendor to make your life easier (and make the competitors’ products less enticing as well). An example of one of these functions is the TOP function in Microsoft SQL Server.  Here’s a faux query, written for Microsoft SQL Server, to find the top 5 students in a class by their course grade:

SELECT TOP 5 (studentID), grade
FROM students
ORDER BY grade DESC

Theoretically, this query would return a list of 5 student IDs and their grades from the table named Students. The ORDER BY clause at the end of the statement ensures that, before the database picks the 5 at the top of the list, the list is sorted according to grade, with the best grades on top. (In other words, the DESC keyword tells the database to sort records in descending order.)

Unfortunately for people working in other databases, like Oracle or MySQL, the TOP function is specific to Microsoft SQL Server.  So, how can we find the top 5 students in other databases?

In MySQL, an equivalent statement would be:

SELECT studentID, grade
FROM students
ORDER BY grade DESC
LIMIT 0,5

Here, the LIMIT command tells the database to display 5 records starting from the 1st record (0), after sorting the records in the table according to the grades.  To display 15 records starting with the 5th record, the last line of the previous statement would be LIMIT 4,15.

Unfortunately, the solution for Oracle is not nearly as simple:

SELECT studentID, grade
FROM (
SELECT studentID, grade, RANK() OVER (ORDER BY grade DESC) rankByGrade
FROM students
)
WHERE
rankByGrade <= 5

Whoa!  What's going on here?  This is a demonstration of a subquery. A subquery is simply a SELECT statement nested inside another SQL statement.  In the above example, the interior SELECT statement (the one that starts on the 3rd line) will be executed first. Any results from the subquery will essentially be treated as a temporary table, existing for the duration of the statement's execution only. This temporary table is used by the exterior query (the SELECT studentID, grade part) as its source of data.

This particular subquery  is using an Oracle function called RANK in association with (ORDER BY grade DESC) to create a column that provides a ranking based on the grade value, from highest to lowest. The highest grade gets a rank of 1, the next highest grade gets a rank of 2, and so on.  In other words, if we were to run just the subquery by itself, the results might look something like:

studentID  grade    rankByGrade
---------  -----    ------
373478     100       1
938937     98.6      2
671037     98.2      3
565422     96.8      4

These results, including the derived rankByGrade number, are then used by the exterior query, which essentially is:
SELECT studentID, grade
FROM [the results of the subquery]
WHERE rankByGrade <= 5

Thus, the exterior query returns only 5 records, which correspond to the records that have a rankByGrade of 5 or less, or, in our case, the top 5 students.

Thanks to Mike Halla, database guru, for providing a sample Oracle query to address this issue.


5 Responses to "How To Retrieve Top (n) Rows from a Database" to “How To Retrieve Top (n) Rows from a Database”

  1. SQL Tutorials Says:

    thanks alot for the post. i was having problems with this .

  2. James Icann Says:

    I also having a problem regarding with this, I hope this will help a lot.

  3. Payday Loan Says:

    I am really inspired along with your writing skills neatly as with the layout in your weblog. Is this a paid subject matter or did you modify it your self? Anyway stay up the excellent quality writing, it’s uncommon to look a nice blog like this one these days..

  4. Kary Says:

    thanks for this post. simplistic and straight to-the-point explanation makes it really easy to understand

  5. Ernie Says:

    Correct Oracle solution:

    SELECT studentID, grade
    FROM students
    WHERE rownum <= 5
    ORDER BY grade DESC;

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.