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.
The SQL TOP
clause is used to limit the number of rows returned. Its basic syntax is:
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.
The MySQL's LIMIT
clause does the same work as SQL TOP
clause. Its basic syntax is:
column_list
FROM table_name
LIMIT number;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:
-- Syntax for MySQL Database
SELECT * FROM employees
ORDER BY salary DESC LIMIT 2, 1;