SQL TOP / MySQL LIMIT Clause

In this tutorial you will learn how to retrieve fixed number of records from the table.

In some situations, you may not be interested in all of the rows returned by a query, for example, if you just want to retrieve the top 10 employees who recently joined the organization, get top 3 students by score, or something like that.

To handle such situations, you can use SQL's TOP clause in your SELECT statement. However the TOP clause is only supported by the SQL Server and MS Access database systems.

MySQL provides an equivalent LIMIT clause, whereas Oracle provides ROWNUM clause for the SELECT statement to restrict the number of rows returned by a query.

SQL TOP Syntax

The SQL TOP clause is used to limit the number of rows returned. Its basic syntax is:

SELECT TOP number | percent column_list FROM table_name;

Here, column_list is a comma separated list of column or field names of a database table (e.g. name, age, country, etc.) whose values you want to fetch. Let's see how it works.

MySQL LIMIT Syntax

The MySQL's LIMIT clause does the same work as SQL TOP clause. Its basic syntax is:

SELECT column_list FROM table_name LIMIT number;

Setting Row Offset in LIMIT Clause

The LIMIT clause accepts an optional second parameter.

When two parameters are specified, the first parameter specifies the offset of the first row to return i.e. the starting point, whereas the second parameter specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

So, if you want to find out the third-highest paid employee, you can do the following:

Example

-- Syntax for MySQL Database 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 2, 1;