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.
TOP clause is used to limit the number of rows returned. Its basic syntax is:
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.
LIMIT clause does the same work as SQL
TOP clause. Its basic syntax is:
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
So, if you want to find out the third-highest paid employee, you can do the following:
-- Syntax for MySQL Database SELECT * FROM employees ORDER BY salary DESC LIMIT 2, 1;