MySQL LIMIT

The LIMIT clause restricts the number of rows returned by a query.

SELECT *
FROM students
LIMIT 5;

This query returns a maximum of five records.

The basic syntax is:

SELECT column1, column2
FROM table_name
LIMIT number;

The number specifies the maximum number of rows to return.

You can use LIMIT 1 when you only need one record.

SELECT *
FROM students
LIMIT 1;

This returns at most one row.

LIMIT 5 returns a maximum of five rows.

SELECT name, course
FROM students
LIMIT 5;

This is useful when displaying a small number of records.

LIMIT is commonly combined with ORDER BY.

SELECT name, fee
FROM students
ORDER BY fee DESC
LIMIT 5;

This returns the five students with the highest fees.

Use ASC with LIMIT to find the lowest values.

SELECT name, fee
FROM students
ORDER BY fee ASC
LIMIT 5;

This returns the five lowest fees.

LIMIT can be used after filtering records with WHERE.

SELECT *
FROM students
WHERE course = 'Python'
LIMIT 5;

This returns up to five Python students.

LIMIT can be combined with LIKE for pattern searches.

SELECT name, course
FROM students
WHERE name LIKE 'A%'
LIMIT 5;

This returns up to five students whose names start with A.

LIMIT can also be used with IN.

SELECT name, course
FROM students
WHERE course IN ('Python', 'Java')
LIMIT 10;

This returns up to ten matching students.

LIMIT can be used after several filtering conditions.

SELECT *
FROM students
WHERE age >= 18
AND fee > 10000
LIMIT 10;

This returns up to ten students matching both conditions.

LIMIT can restrict the number of unique values returned by DISTINCT.

SELECT DISTINCT course
FROM students
LIMIT 5;

This returns up to five unique courses.

LIMIT can restrict the number of grouped results.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
LIMIT 5;

This returns up to five course groups.

LIMIT can be used after aggregate results have been generated.

SELECT course, AVG(fee) AS average_fee
FROM students
GROUP BY course
ORDER BY average_fee DESC
LIMIT 3;

This returns the three courses with the highest average fee.

LIMIT can be used with an offset to skip some rows before returning results.

SELECT *
FROM students
LIMIT 5 OFFSET 10;

This skips the first ten rows and returns up to five rows.

The syntax is:

SELECT *
FROM table_name
LIMIT row_count OFFSET offset_value;

row_count specifies how many rows to return, while offset_value specifies how many rows to skip.

SELECT id, name, course
FROM students
LIMIT 10 OFFSET 20;

This skips the first 20 rows and returns the next 10 rows.

MySQL also supports:

SELECT *
FROM students
LIMIT 20, 10;

Here, 20 is the offset and 10 is the number of rows to return.

This is equivalent to:

SELECT *
FROM students
LIMIT 10 OFFSET 20;

LIMIT and OFFSET are commonly used to create pagination in websites.

SELECT *
FROM students
ORDER BY id
LIMIT 10 OFFSET 0;

This can represent the first page when displaying ten records per page.

If each page contains ten records, the second page can skip the first ten records.

SELECT *
FROM students
ORDER BY id
LIMIT 10 OFFSET 10;

This returns the next ten records.

The third page can skip twenty records.

SELECT *
FROM students
ORDER BY id
LIMIT 10 OFFSET 20;

This returns records 21 to 30 when the result is consistently ordered by id.

LIMIT can also be used with JOIN queries.

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course = courses.course_name
LIMIT 10;

This returns up to ten rows from the joined result.

MySQL can use LIMIT with DELETE to restrict the number of rows deleted.

DELETE FROM students
WHERE course = 'Test'
LIMIT 5;

This deletes up to five matching records.

LIMIT can also restrict the number of rows affected by an UPDATE.

UPDATE students
SET fee = fee + 500
WHERE course = 'Python'
LIMIT 5;

This updates up to five matching Python student records.

LIMIT can be applied to queries that use subqueries.

SELECT *
FROM (
    SELECT name, fee
    FROM students
    ORDER BY fee DESC
    LIMIT 5
) AS top_students;

The inner query first selects the five highest fees.

When using LIMIT for pagination or ranking, it is a good practice to use ORDER BY so that the result has a defined order.

SELECT *
FROM students
ORDER BY id ASC
LIMIT 10;

Sorting by a suitable column such as an ID helps keep pages consistent.

• Using the wrong number of rows
• Confusing LIMIT with OFFSET
• Forgetting ORDER BY when consistent ordering is important
• Using the wrong offset for pagination
• Assuming LIMIT changes the table permanently
• Using UPDATE or DELETE with LIMIT without checking the affected records

A simple LIMIT workflow is:

1. Select the required columns.
2. Choose the table.
3. Use WHERE if filtering is required.
4. Use ORDER BY when a specific order is needed.
5. Use LIMIT to restrict the number of rows.
6. Use OFFSET for pagination.

SELECT id, name, course, fee
FROM students
WHERE age >= 18
ORDER BY fee DESC
LIMIT 10 OFFSET 0;

To find the five students with the highest fees:

SELECT id, name, course, fee
FROM students
ORDER BY fee DESC
LIMIT 5;

ORDER BY sorts the data and LIMIT selects only the first five records.

Suppose a student management page displays ten students per page.

-- Page 1
SELECT *
FROM students
ORDER BY id ASC
LIMIT 10 OFFSET 0;

-- Page 2
SELECT *
FROM students
ORDER BY id ASC
LIMIT 10 OFFSET 10;

-- Page 3
SELECT *
FROM students
ORDER BY id ASC
LIMIT 10 OFFSET 20;

This allows the application to display records page by page.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2),
    admission_date DATE
);

INSERT INTO students
(name, course, age, fee, admission_date)
VALUES
('Amit', 'Python', 22, 15000.00, '2026-01-15'),
('Priya', 'Java', 21, 18000.00, '2026-02-10'),
('Rahul', 'PHP', 24, 12000.00, '2026-03-05'),
('Neha', 'Python', 20, 20000.00, '2026-04-12'),
('Ravi', 'JavaScript', 26, 22000.00, '2026-05-20'),
('Anita', 'Java', 23, 17000.00, '2026-06-15');

SELECT id, name, course, age, fee
FROM students
WHERE age >= 20
ORDER BY fee DESC
LIMIT 3;

This query finds students aged 20 or above, sorts them by fee from highest to lowest, and returns only the top three records.