Basic about SELECT LIMIT Statement – using and example

This SQL tutorial explains how to use the SELECT LIMIT announcement in SQL with syntax and examples.

Description

The SQL SELECT LIMIT assertion is used to retrieve documents from one or extra tables in a database and restrict the quantity of records again based on a limit value.

Syntax

The syntax for the SELECT LIMIT assertion 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 desire to retrieve. tables The tables that you wish to retrieve records from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. The stipulations that should be met for the documents to be selected. ORDER BY expression Optional. It is used in the SELECT LIMIT statement so that you can order the effects and goal those records that you desire to return. ASC is ascending order and DESC is descending order. LIMIT number_rows It specifies a restrained variety of rows in the end result set to be lower back based on number_rows. For example, LIMIT 10 would return the first 10 rows matching the SELECT criteria. This is where sort order things so be positive to use an ORDER BY clause appropriately. OFFSET offset_value Optional. The first row back by using LIMIT will be decided by means of offset_value.

Example – Using LIMIT keyword

Let’s look at how to use a SELECT assertion 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 choose the first 5 archives from the contacts table where the internet site is ‘TechOnTheNet.com’. Note that the outcomes are sorted by using contact_id in descending order so this means that the 5 largest contact_id values will be back by the SELECT LIMIT statement.

If there are different data in the contacts desk that have a internet site value of ‘TechOnTheNet.com’, they will no longer be back by the SELECT LIMIT announcement in SQL.

If we desired to select the 5 smallest contact_id values alternatively of the largest, we may want to exchange the kind 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 effects would be sorted by way of contact_id in ascending order, so the first 5 smallest contact_id records that have a website of ‘TechOnTheNet.com’ would be lower back by this SELECT LIMIT statement. No different information would be again via this query.

Example – Using OFFSET keyword

The offset keyword approves you to offset the first document lower back by means of the LIMIT clause. For example:

LIMIT 3 OFFSET 1

This LIMIT clause would return 3 archives in the end result set with an offset of 1. What this capability is that the SELECT announcement would bypass the first report that would normally be lower back and alternatively return the second, third, and fourth records.

Let’s look at how to use a SELECT LIMIT announcement 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 instance makes use of an OFFSET of two which ability that the first and 2d documents in the end result set will be skipped…and then the subsequent 5 rows will be returned.