SQL LIMIT Clause (TOP or ROWNUM)

SQL LIMIT, TOP, ROWNUM Clause

This is a detailed guide on the SQL Limit Clause which is also called TOP and ROWNUM. Learn how to return a specific number of records using this clause.

Use of SQL LIMIT or TOP or ROWNUM Clause

First of all, to clear the confusion of all the beginners out there, LIMIT or TOP or ROWNUM are the different keywords for the same thing for different SQL databases. SQL Server or MS Access makes use of the TOP keyword. In MySQL, we use the LIMIT keyword and in Oracle, the ROWNUM keyword is used.

The purpose of all of these is to return or filter only a specific number of records.

Syntax

As the keyword is different for different SQL based databases, therefore syntax is also different.

MySQL Syntax for Limit

MySQL Syntax for Limit & Offset

You can also set offset along with the Limit clause in MySQL. This will help you get a particular number of database table records after skipping a specific number of records which is known as offset.

Note. You can also add a comma instead of writing the OFFSET keyword but for this, you should write the offset_number first and the count_number after it.

MS Access or SQL Server Syntax

Oracle Syntax

LIMIT, TOP, and ROWNUM Examples

There are several different examples given below that illustrate the use cases for LIMIT, TOP and ROWNUM clauses in SQL. For the demonstration, I have got a demo database with the students table. Given below is the screenshot of the columns and data of this table.

SQL LIMIT TOP ROWNUM Example Database

MySQL Basic Limit Example

The following query will select the first 5 rows of this table.

MySQL Limit Basic Example

You can see in the screenshot of the result that only the first 5 table records are fetched using the SQL Select Statement.

MySQL LIMIT With OFFSET Example

Offset is helpful in different use cases. One of the most common use cases is the pagination feature in blogs and websites. Offset skips the first few records specified after the comma or OFFSET keyword.

The following query with LIMIT clause will select only 5 records after skipping the first 3 records of the table.

Or

Note. Observe carefully in the second query, the offset is written first and then the count is written.

MySQL LIMIT And OFFSET Example

You can clearly observe that in the above result set the first three rows of the table are skipped. The Limit Clause is often used with the OFFSET keyword for queries of the blog post pagination system. For example in a search engine that displays 10 results in one page, for the second page, the query for the second page will like this. It will select 10 records with offset 10 which will skip the first 10 results.

MySQL Limit With WHERE Clause

Any other clause like SQL WHERE clause could be added along with the LIMIT clause to filter the results in a more useful way. One such example is given below that filters the first 5 students who’re born before the year 2000.

MySQL Limit Query With WHERE Clause

You can see all the records in the result set are having the value of the dob column before the year 2000.

MS Access or SQL Server Example

The following query will fetch the first 3 records from the table named students in an MS Access or SQL Server database.

MS Access or SQL Server Example With WHERE Clause

Query given below will fetch the first 5 records from the table named students where the column section_id value is 5.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

TOP Percent Example

The following query will fetch the first 30% of the records of the database table students.

ROWNUM Example For Oracle Databases

In Oracle databases, you can use the ROWNUM keyword. The following example query will fetch the first 5 rows from the students table. For ROWNUM, you’ve to use the WHERE clause because ROWNUM is a kind of variable that will define the number of rows to be included in the resultset.

Helpful Associated Guides.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *