SQL: SELECT LIMIT Statement
This SQL tutorial explains how to use the SELECT LIMIT statement in SQL with syntax and examples.
Description
The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value.
TIP: SELECT LIMIT is not supported in all SQL databases.
For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results. The SELECT TOP statement is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
Syntax
The syntax for the SELECT LIMIT statement in SQL is:
SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_rows [ OFFSET offset_value ];
Parameters or Arguments
- expressions
- The columns or calculations that you wish to retrieve.
- tables
- The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
- WHERE conditions
- Optional. The conditions that must be met for the records to be selected.
- ORDER BY expression
- Optional. It is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.
- LIMIT number_rows
- It specifies a limited number of rows in the result set to be returned based on number_rows. For example, LIMIT 10 would return the first 10 rows matching the SELECT criteria. This is where sort order matters so be sure to use an ORDER BY clause appropriately.
- OFFSET offset_value
- Optional. The first row returned by LIMIT will be determined by offset_value.
Example - Using LIMIT keyword
Let's look at how to use a SELECT statement with a LIMIT clause in SQL.
For example:
SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 5;
This SQL SELECT LIMIT example would select the first 5 records from the contacts table where the website is 'TechOnTheNet.com'. Note that the results are sorted by contact_id in descending order so this means that the 5 largest contact_id values will be returned by the SELECT LIMIT statement.
If there are other records in the contacts table that have a website value of 'TechOnTheNet.com', they will not be returned by the SELECT LIMIT statement in SQL.
If we wanted to select the 5 smallest contact_id values instead of the largest, we could change the sort order as follows:
SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id ASC LIMIT 5;
Now the results would be sorted by contact_id in ascending order, so the first 5 smallest contact_id records that have a website of 'TechOnTheNet.com' would be returned by this SELECT LIMIT statement. No other records would be returned by this query.
Example - Using OFFSET keyword
The offset keyword allows you to offset the first record returned by the LIMIT clause. For example:
LIMIT 3 OFFSET 1
This LIMIT clause would return 3 records in the result set with an offset of 1. What this means is that the SELECT statement would skip the first record that would normally be returned and instead return the second, third, and fourth records.
Let's look at how to use a SELECT LIMIT statement with an OFFSET clause in SQL.
For example:
SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 5 OFFSET 2;
This SQL SELECT LIMIT example uses an OFFSET of 2 which means that the first and second records in the result set will be skipped...and then the next 5 rows will be returned.
Advertisements