The LIMIT clause in MySQL is used to limit the number of rows returned by a query. It allows you to specify the maximum number of rows to be retrieved from the result set. Here's an example of using the LIMIT clause:

Consider a table called products with the following structure:

Syntax:

                
    CREATE TABLE products (
      product_id INT PRIMARY KEY,
      name VARCHAR(50),
      price DECIMAL(10,2)
    );
                
            

Suppose we want to retrieve the top 5 expensive products from the products table. We can use the LIMIT clause as follows:

                
    SELECT * FROM products ORDER BY price DESC LIMIT 5;
                
            

In the above example, the LIMIT clause is used to restrict the result set to a maximum of 5 rows. The query first sorts the products by the price column in descending order (DESC), and then it limits the result set to the top 5 rows.

The LIMIT clause is commonly used to implement pagination or to retrieve a specific number of rows from a larger result set. By specifying an offset along with the limit, you can retrieve rows from a specific position. Here's an example:

                
    SELECT * FROM products LIMIT 10, 5;
                
            

In the above example, the query retrieves 5 rows starting from the 11th row (offset 10). This allows you to retrieve rows in batches or pages.

Remember to adjust the table name and column names in the examples based on your specific scenario. The LIMIT clause can be used in combination with other clauses like ORDER BY, WHERE, or GROUP BY to further refine your query results and retrieve a specific subset of rows.