MySQL LIMIT
Control the number of rows returned in your query results
🎯 What is MySQL LIMIT?
The LIMIT clause restricts the number of rows returned by a SELECT query. It's perfect for displaying top results, pagination, or testing queries with large datasets efficiently.
-- Get first 5 customers
SELECT * FROM customers
LIMIT 5;
Key LIMIT Concepts
Basic LIMIT
Limit number of rows
SELECT * FROM products
LIMIT 10;OFFSET
Skip rows before limiting
SELECT * FROM products
LIMIT 10 OFFSET 5;Pagination
Display data in pages
SELECT * FROM orders
LIMIT 20 OFFSET 40;Top Results
Get highest or lowest values
SELECT * FROM sales
ORDER BY amount DESC
LIMIT 3;🔹 Basic LIMIT Syntax
The LIMIT clause is placed at the end of your SELECT statement to restrict how many rows are returned. This is useful when working with large tables or when you only need a sample of data for testing purposes.
-- Get first 3 employees
SELECT employee_id, name, salary
FROM employees
LIMIT 3;Output:
| employee_id | name | salary |
|---|---|---|
| 1 | John Smith | 50000 |
| 2 | Sarah Johnson | 55000 |
| 3 | Mike Davis | 48000 |
🔹 LIMIT with OFFSET
OFFSET allows you to skip a specific number of rows before applying the LIMIT. This is essential for pagination where you need to display different pages of results. The first number is the limit, and OFFSET specifies how many rows to skip.
-- Skip first 2 rows, then get next 3
SELECT product_name, price
FROM products
LIMIT 3 OFFSET 2;
-- Alternative syntax (older MySQL versions)
SELECT product_name, price
FROM products
LIMIT 2, 3; -- OFFSET, LIMITOutput:
| product_name | price |
|---|---|
| Laptop | 899.99 |
| Mouse | 25.50 |
| Keyboard | 75.00 |
🔹 LIMIT with ORDER BY
Combining LIMIT with ORDER BY lets you get the top or bottom records based on specific criteria. Always use ORDER BY before LIMIT to ensure you get meaningful results like highest sales, newest records, or lowest prices.
-- Get top 5 highest paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
-- Get 3 cheapest products
SELECT product_name, price
FROM products
ORDER BY price ASC
LIMIT 3;Output (Top 5 Salaries):
| name | salary |
|---|---|
| Alice Brown | 95000 |
| Bob Wilson | 87000 |
| Carol Lee | 82000 |
🔹 Pagination Example
Pagination divides large result sets into manageable pages. Calculate OFFSET by multiplying page size by the page number minus one. For example, page 1 starts at offset 0, page 2 at offset 10, and so on with a page size of 10.
-- Page 1 (rows 1-10)
SELECT * FROM customers
LIMIT 10 OFFSET 0;
-- Page 2 (rows 11-20)
SELECT * FROM customers
LIMIT 10 OFFSET 10;
-- Page 3 (rows 21-30)
SELECT * FROM customers
LIMIT 10 OFFSET 20;Pagination Formula:
OFFSET = (page_number - 1) × page_size
- Page 1: OFFSET 0
- Page 2: OFFSET 10
- Page 3: OFFSET 20
🔹 Practical Examples
Here are real-world scenarios where LIMIT is commonly used in applications and data analysis tasks:
-- Get latest 5 blog posts
SELECT title, published_date
FROM blog_posts
ORDER BY published_date DESC
LIMIT 5;
-- Get random 10 products (with RAND())
SELECT product_name, price
FROM products
ORDER BY RAND()
LIMIT 10;
-- Get first customer for testing
SELECT * FROM customers
LIMIT 1;