SELECT with LIMIT

When a query returns many rows, but only a few of them needs to be seen, add a LIMIT clause.  This is a MySQL option that allows the output of a query to be  limited to the first rows of the result that would otherwise be returned. 

The LIMIT clause may be given with either one or two arguments:

ƒ LIMIT row_count

ƒ LIMIT skip_count, row_count

limit,h

Each argument must be represented as an integer constant.  Expressions, user variables, and so forth can not be used.

First Rows from the Beginning When followed by a single integer, row_count, LIMIT returns the first row_count rows from the beginning of the result set. To select just the first 8 rows of a result set, use LIMIT 8;

mysql> SELECT ID, Name FROM City LIMIT 8;

+----+-------------------+

| ID | Name               |

+----+-------------------+

|  1 | Kabul                |

|  2 | Qandahar         |

|  3 | Herat                 |

|  4 | Mazar-e-Sharif |

|  5 | Amsterdam       |

|  6 | Rotterdam         |

|  7 | Haag                  |

|  8 | Utrecht               |

+----+--------------------+

8 rows in set (#.## sec)

Skipping Rows 

When followed by two integers, skip_count and row_count, LIMIT skips the first  skip_count 

rows from the beginning of the result set, and then returns the next row_count rows.  To skip the first 20 rows of a result set and then retrieve the next 8, use the following; 

mysql> SELECT ID, Name FROM City LIMIT 20,8; 

+----+-------------------------+ 

| ID | Name                       | 

+----+-------------------------+ 

| 21 | Amersfoort             |  

| 22 | Maastricht              |  

| 23 | Dordrecht               |  

| 24 | Leiden                    |  

| 25 | Haarlemmermeer |  

| 26 | Zoetermeer           |  

| 27 | Emmen                  |  

| 28 | Zwolle                    |  

+----+------------------------+ 

8 rows in set (#.## sec) 

Limit with ORDER BY 

LIMIT is especially useful in conjunction with the ORDER BY clause, to put the rows in a particular order. When they are used together, MySQL applies ORDER BY first and then LIMIT.  One common use for this is to find the row containing the smallest or largest values in a particular column.  For example, to find a row table t containing the smallest id value, use this statement; 

SELECT * FROM t ORDER BY id LIMIT 1;  

The following query selects the first 5 highest country populations; 

mysql> SELECT Name, Population FROM Country 

         -> ORDER BY population DESC LIMIT 5; 

+--------------------+------------------+ 

| name                 | population     | 

+--------------------+------------------+ 

| China                | 1277558000 | 

| India                  | 1013662000 | 

| United States   |  278357000  | 

| Indonesia          |  212107000  | 

| Brazil                 |  170115000  | 

+--------------------+-------------------+ 

5 rows in set (#.## sec)