How to Limit Results in Oracle

Problem:

You want to limit the number of rows resulting from a query in Oracle.

Example:

In the exam table, there are names of the students with the results of the exam.

You want to get the three rows with the best exam results.

Solution:

SELECT *
FROM (
  SELECT *
  FROM exam
  ORDER BY exam_result DESC
)
WHERE ROWNUM <= 3;

The result of the query looks like this:

Discussion:

In the FROM clause, use a subquery that sorts the rows by the exam_result column in descending order.

SELECT *
FROM exam
ORDER BY exam_result DESC

In the main query, write a condition that the number of rows should be less than or equal to 3. In Oracle you can use the pseudocolumn ROWNUM (the row number) to do it. To make sure you'll see only the first three rows, you need a condition that the row number should be less than or equal to 3.

WHERE ROWNUM <= 3

You need a subquery just to sort the rows. If you'd like three rows randomly instead of the best three, simply write the table name instead of the subquery.

SELECT *
FROM exam
WHERE ROWNUM <= 3;

Watch out! This will not work when you're looking for rows with a number greater than some value (for example, ROWNUM > 2 will not return any rows.)