MySQL LIMIT - Number of Rows

The LIMIT command is a clause in the SQL language that is used to limit the number of rows returned from a query.

In MySQL, the LIMIT command is used to specify the number of records to return in a result set.

The syntax of the LIMIT command is as follows: 

SELECT column1, column2, ...
FROM table_name
LIMIT number;

The number parameter specifies the maximum number of rows to return. For example, if you want to retrieve only the first 10 rows from a table, you can use the following command: 

SELECT *
FROM my_table
LIMIT 10;

This will return only the first 10 rows from the my_table table.

In addition to the number parameter, the LIMIT command can also take an optional offset parameter. 

This is used to specify the starting point for the result set. For example, if you want to retrieve rows 11 to 20 from a table, you can use the following command:

SELECT *
FROM my_table
LIMIT 10 OFFSET 10;

This will skip the first 10 rows and return the next 10 rows starting from the 11th row.

Content of our table:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

LIMIT example:

mysql> SELECT * FROM friends LIMIT 2;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

The query selects all columns (*) from the friends table and limits the result set to 2 rows. 

The output shows the 2 rows that were returned by the query, along with their corresponding values for each column. 

We can target specific columns:

mysql> SELECT id, Name, Telephone FROM friends LIMIT 2;
+----+------+-----------+
| id | Name | Telephone |
+----+------+-----------+
| 15 | Ana  |    111222 |
| 16 | Ana  |    555666 |
+----+------+-----------+
2 rows in set (0.00 sec)

mysql>