MySQL SQL Limit Feature: The SQL LIMIT Keyword

In MySQL, you can use the LIMIT clause to restrict the number of rows returned by a SELECT query. You provide two parameters: the offset number, and the count (the maximum number of rows to be returned).

The syntax of this in MySQL is:

SELECT columns
FROM table
LIMIT offset, count;

We can use this on our sample database. Let’s say we wanted to find the top 5 customers by revenue in descending order. Our query would look like this:

This table shows the top 5 results ordered by revenue in descending order. The LIMIT clause in MySQL is easy to use and is the most common way of limiting the top results in MySQL. For more information on the MySQL row limiting feature, look at the official documentation.

SQL Server SQL Limit Feature: The SQL Top Keyword

The way to perform row limiting in SQL Server is different from doing it in MySQL.

In SQL Server, you use the SQL TOP keyword rather than LIMIT. The SQL TOP keyword goes at the start of the query in the SELECT clause.

The syntax of a SELECT query that uses TOP is:

SELECT TOP number|percent columns
FROM table;

The word TOP goes just after the word SELECT, just like the DISTINCT keyword.

You can then specify either the number of rows to display or the percentage of rows from the result set to display.

Let’s look at an example using our sample data. If we wanted to find the same data as the earlier example (the top 5 customers ordered by revenue in descending order), our SQL query would look like this:

SELECT TOP 5 customer_id, revenue
FROM customer_revenue
ORDER BY revenue DESC;

The output for this query would be:

CUSTOMER_ID

REVENUE

2

9384760

10

5131750

11

4431791

8

4341421

4

3596297

It’s the same result as the MySQL LIMIT example. It shows the top 5 customers by revenue.

Both the MySQL and SQL Server features for row limiting or showing the top-N queries are simple. What about Oracle?

Oracle Row Limiting Clause

Up until Oracle 12c, performing row limiting or top-N queries was harder. There was no keyword you could use, so you had to use one of several solutions. I’ve outlined those later in this guide if you’re not using 12c or higher.

We’ve added the PERCENT keyword into the FETCH clause to indicate we only want to see the top 25% of rows. This top 25% is calculated by ordering the revenue in descending order, as specified by the ORDER BY clause.

The results of this query are:

CUSTOMER_ID

REVENUE

2

9384760

10

5131750

11

4431791

8

4341421

There are 4 rows shown because the table has 13 rows. 25% of 13 is 3.25, which is rounded up to 4.

You can use the same concept to get the last percentage of rows. Rather than changing the FIRST keyword to LAST, you can change the ORDER BY to sort in the opposite direction:

Select the Top 100 Rows in Oracle SQL

To find the top 100 rows in a query in Oracle SQL, you can use the FETCH parameter and specify FETCH FIRST 100 ROWS ONLY. Add an ORDER BY clause to your query to define how the data is ordered, and the data will be displayed.

Row Limiting Without the FETCH Clause

There are a few ways to do row limiting and top-N queries without the FETCH clause in Oracle. This could be because you’re not working on a 12c or 18c database. Perhaps you’re running Oracle Express 11g, or using an 11g database at work.

There are a few ways to do this.

Using an Inline View with ROWNUM

You can use an inline view with the ROWNUM pseudocolumn to perform top-N queries. This is one of the most common ways to do row limiting without the FETCH clause.

SELECT customer_id, revenue
FROM customer_revenue
WHERE ROWNUM <= 5
ORDER BY revenue DESC;

This will perform the limiting on the row number before the ordering, and give us these results:

CUSTOMER_ID

REVENUE

2

9384760

4

3596297

3

1852828

1

109470

5

82495

It has limited the results to the first 5 rows it has found in the table, and then performed the ordering. This will likely give you results you are not expecting. This is why we use a subquery such as an inline view.

Pagination with ROWNUM in Oracle

We can use this method to perform pagination of records in SQL. While it looks a little messier than the FETCH and OFFSET methods, it does work well:

The difference between these two functions is that the rank numbers that are assigned do not include gaps. However, it still shows us the top 6 rows as there is a tie.

CUSTOMER_ID

REVENUE

2

9384760

10

5131750

11

4431791

8

4341421

4

3596297

13

3596297

Using ROW_NUMBER For Top-N Queries

You can use the Oracle analytic function ROW_NUMBER to write top-N queries. It’s similar to the RANK function. The ROW_NUMBER function assigns a unique number for each row returned, but can be used over a window of data (just like all analytic queries).

A query to find the top 5 customers by revenue using ROW_NUMBER would look like this: