Skip to main content

LIMIT

Learning Focus

Use this lesson to understand LIMIT with practical syntax and examples.

Concept Overview​

What is LIMIT in MySQL?​

LIMIT is a clause in SQL used to control the number of rows returned by a query. It allows you to fetch a specific number of records from a result set.

Why is LIMIT Important?​

  • Reduces the amount of data processed and displayed
  • Optimizes performance for large datasets
  • Useful in pagination and previews

Where It Fits in MySQL​

LIMIT is commonly used with SELECT statements and plays a key role in:

  • Pagination (e.g., in web apps showing 10 items per page)
  • Data analysis (viewing top-N records)
  • Debugging (testing queries with smaller outputs)

Basic Syntax & Rules​

Basic Syntax​

SELECT column1, column2 FROM table_name
LIMIT [offset,] row_count;

Parameters​

  • row_count: Required. Number of rows to return.
  • offset (optional): Number of rows to skip before starting to return results.

Alternate Syntax​

LIMIT row_count OFFSET offset;

Notes​

  • LIMIT applies after filtering and ordering (i.e., after WHERE, ORDER BY)
  • If you don't specify offset, results start from the first row
  • LIMIT is MySQL-specific; other databases may use TOP or FETCH

Step-by-Step Examples​

Example 1: Simple Limit​

Query

SELECT * FROM employees
LIMIT 5;

Description

  • Returns the first 5 employees.

Expected Output (ASCII)

+----+------------+-----------+
| ID | FirstName  | LastName  |
+----+------------+-----------+
|  1 | Alice      | Smith     |
|  2 | Bob        | Johnson   |
|  3 | Carol      | Lee       |
|  4 | David      | Wilson    |
|  5 | Eva        | Kim       |
+----+------------+-----------+

Example 2: With Offset​

Query

SELECT * FROM employees
LIMIT 5 OFFSET 5;

Description

  • Skips the first 5 employees and returns the next 5.

Example 3: Using Offset as First Parameter​

SELECT * FROM employees
LIMIT 5, 5;

Same as above: Returns rows 6 to 10.

Example 4: Top N Products by Price​

SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

Description

  • Gets the top 3 most expensive products.

Example 5: Paginate Customers​

-- Page 2 (assuming 10 per page)
SELECT * FROM customers
ORDER BY id
LIMIT 10 OFFSET 10;

Practical Use Cases​

1. Pagination in Web Apps​

Display 10 blog posts per page.

SELECT * FROM posts
ORDER BY published_date DESC
LIMIT 10 OFFSET 20;

2. Top-N Analysis​

Find top 5 highest-paid employees.

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

3. Sampling Data​

Preview small samples from big datasets.

SELECT * FROM logs
LIMIT 100;

4. Dashboards & Reports​

Show only the top 3 selling products.

SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 3;

5. Performance Testing​

Run a complex query on a smaller slice.

Common Mistakes & Troubleshooting​

Mistake 1: Incorrect Order of LIMIT​

SELECT * FROM table OFFSET 10 LIMIT 5; -- [X] Invalid in MySQL

Fix:

SELECT * FROM table LIMIT 5 OFFSET 10; -- [OK]

Mistake 2: Using LIMIT Without ORDER BY​

SELECT * FROM users LIMIT 10;

  • Might return unpredictable results if no ORDER BY

Fix:

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

Mistake 3: Thinking LIMIT filters rows​

It does not act like WHERE. It just trims output after filtering.

Tip: Debugging with Sample Data​

Use LIMIT 1 to inspect one row at a time.

Best Practices​

  • Always combine LIMIT with ORDER BY for deterministic results
  • Use LIMIT in testing/dev environments to avoid loading full tables
  • Paginate smartly by keeping track of page number and offset
  • Avoid excessive offset in large tables (performance degrades with high OFFSET)

Hands-On Practice​

Setup Sample Table​

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees (name, department, salary)
VALUES
('Alice', 'HR', 50000),
('Bob', 'IT', 70000),
('Carol', 'Finance', 65000),
('David', 'IT', 80000),
('Eva', 'HR', 55000),
('Frank', 'Finance', 60000),
('Grace', 'IT', 75000);

Exercise 1: Return the first 3 employees​

-- Your Query:
SELECT * FROM employees
LIMIT 3;

Expected Output:

Alice, Bob, Carol

Exercise 2: Return employees 4-6​

-- Your Query:
SELECT * FROM employees
LIMIT 3 OFFSET 3;

Exercise 3: Top 2 salaries​

-- Your Query:
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 2;

Expected Output:

David, Grace

Connection to Other Concepts​

Related ConceptDescription
SELECTLIMIT is used with SELECT queries
ORDER BYOften paired to control output order
OFFSETSkips rows before applying LIMIT
WHEREFilters rows before LIMIT applies
JOINCombine tables, then limit result set
GROUP BYLimit after aggregation for summary

Prerequisites​

  • Understanding SELECT, ORDER BY, WHERE

Learn Next​

  • JOIN, GROUP BY, HAVING, UNION, Window functions

Visual Learning Diagram​

graph TD
  A[SELECT Statement] --> B[WHERE Clause]
  B --> C[ORDER BY Clause]
  C --> D((LIMIT)):::currentTopic
  D --> E[Pagination]
  D --> F[Top-N Queries]
  D --> G[Data Sampling]

  subgraph Prerequisites
    A
    B
    C
  end

  subgraph Next Concepts
    E
    F
    G
  end

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef currentTopic stroke-width:4px
  class A,B,C,D,E,F,G allNodes
  class D currentTopic

Common Pitfalls​

PitfallConsequencePrevention
Executing queries without validating sample rowsLogic errors reach production data or reportsStart with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behaviorAggregations and filters return misleading resultsTest with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schemaQueries fail due to missing columns/indexesVerify structure with DESCRIBE table_name; and adapt query design

Quick Reference​

SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name ORDER BY created_at DESC LIMIT 20 OFFSET 40;

What's Next​