SELECT with a LIMIT clause

Query:

SELECT *
  FROM Customers
 ORDER BY CustomerID 
 LIMIT 3;

Result:

<th>CustomerID</th>
<th>CustomerName</th>
<th>ContactName</th>
<th>Address</th>
<th>City</th>
<th>PostalCode</th>
<th>Country</th>

<td>1<br><br></td>
<td>Alfreds Futterkiste</td>
<td>Maria Anders</td>
<td>Obere Str. 57</td>
<td>Berlin</td>
<td>12209</td>
<td>Germany</td>

<td>2</td>
<td>Ana Trujillo Emparedados y helados</td>
<td>Ana Trujillo</td>
<td>Avda. de la Constitución 2222</td>
<td>México D.F.</td>
<td>05021</td>
<td>Mexico</td>

<td>3</td>
<td>Antonio Moreno Taquería</td>
<td>Antonio Moreno</td>
<td>Mataderos 2312</td>
<td>México D.F.</td>
<td>05023</td>
<td>Mexico</td>

Best Practice Always use ORDER BY when using LIMIT; otherwise the rows you will get will be unpredictable.

Query:

SELECT *
  FROM Customers
 ORDER BY CustomerID 
 LIMIT 2,1;

Explanation:

When a LIMIT clause contains two numbers, it is interpreted as LIMIT offset,count. So, in this example the query skips two records and returns one.

Result:

<th>CustomerID</th>
<th>CustomerName</th>
<th>ContactName</th>
<th>Address</th>
<th>City</th>
<th>PostalCode</th>
<th>Country</th>

<td>3</td>
<td>Antonio Moreno Taquería</td>
<td>Antonio Moreno</td>
<td>Mataderos 2312</td>
<td>México D.F.</td>
<td>05023</td>
<td>Mexico</td>

Note:

The values in LIMIT clauses must be constants; they may not be column values.

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents