• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL LIMIT

← Previous Next →

In MySQL, the LIMIT clause is used to restrict the number of rows returned by a query. It is particularly useful when working with large datasets, where fetching every row would be inefficient or unnecessary. LIMIT allows you to retrieve a subset of rows for faster processing, pagination, or testing.

LIMIT is often combined with ORDER BY to control which rows are retrieved, ensuring the data returned is meaningful and in the desired order.

Basic Syntax

The simplest syntax of LIMIT is:

SELECT column1, column2
FROM table_name
LIMIT number_of_rows;

  • number_of_rows specifies how many rows you want to retrieve.

  • MySQL will return the first N rows based on the default or specified order.

Example:

SELECT name, age FROM students
LIMIT 3;

  • Returns the first 3 students from the table.

Using LIMIT with OFFSET

LIMIT can also be combined with an offset to skip a certain number of rows:

SELECT name, age FROM students
LIMIT offset, number_of_rows;

  • offset specifies how many rows to skip.

  • number_of_rows specifies how many rows to return.

Example:

SELECT name, age FROM students
LIMIT 2, 3;

  • Skips the first 2 rows and returns the next 3 rows.

  • This is particularly useful for pagination in web applications.

LIMIT with ORDER BY

LIMIT is often used together with ORDER BY to fetch top or bottom records:

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

  • Retrieves the 5 oldest students.

  • Using ORDER BY ensures the rows returned are sorted in a meaningful order.

Example with OFFSET:

SELECT name, age FROM students
ORDER BY age ASC
LIMIT 2, 3;

  • Skips the two youngest students and retrieves the next three in ascending order.

Use Cases of LIMIT

1. Pagination

LIMIT is commonly used to display a limited number of results per page:

SELECT name, age FROM students
ORDER BY name
LIMIT 0, 10;  -- first page

  • Next page:

SELECT name, age FROM students
ORDER BY name
LIMIT 10, 10;  -- second page

  • OFFSET increases by the page size each time, enabling smooth navigation.

2. Fetch Top or Bottom Records

LIMIT helps quickly retrieve top-performing or lowest-performing entries:

SELECT name, score FROM students
ORDER BY score DESC
LIMIT 3;  -- top 3 scores

  • Similarly, changing ORDER BY to ascending gives the lowest scores.

3. Testing Queries

When working with large tables, LIMIT can help test queries without fetching the entire dataset:

SELECT * FROM students
LIMIT 5;

  • Returns only 5 rows, allowing quick verification of query logic.

LIMIT with UPDATE or DELETE

MySQL allows LIMIT in UPDATE and DELETE statements to control how many rows are affected:

Update example:

UPDATE students
SET age = age + 1
ORDER BY age ASC
LIMIT 2;

  • Increments the age of the two youngest students.

Delete example:

DELETE FROM students
WHERE city = 'Mumbai'
LIMIT 1;

  • Deletes only one student from Mumbai.

  • Using LIMIT prevents accidental mass updates or deletions.

Important Points

  1. LIMIT is not standard SQL, but it is widely supported in MySQL.

  2. When using LIMIT without ORDER BY, the returned rows may be arbitrary. Always combine with ORDER BY for consistent results.

  3. OFFSET in LIMIT offset, row_count starts counting from 0.

  4. You can also use LIMIT row_count OFFSET offset, which is equivalent:

SELECT name, age FROM students
LIMIT 3 OFFSET 2;

  • Skips 2 rows and returns the next 3 rows.

Real-Life Example

Suppose the students table contains:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Pune
3 Pooja 18 Pune
4 Riya 21 Delhi
5 Ananya 22 Mumbai

Example 1 – Fetch first 3 students:

SELECT name, age FROM students
LIMIT 3;

  • Returns: Aditi, Meera, Pooja

Example 2 – Fetch 3 students after skipping first 2:

SELECT name, age FROM students
LIMIT 2, 3;

  • Returns: Pooja, Riya, Ananya

Example 3 – Fetch top 2 oldest students:

SELECT name, age FROM students
ORDER BY age DESC
LIMIT 2;

  • Returns: Ananya, Riya

Example 4 – Pagination for 2 students per page, page 2:

SELECT name, age FROM students
ORDER BY name
LIMIT 2, 2;

  • Skips first 2 alphabetically sorted students and returns next 2.

Summary of the Tutorial

  • LIMIT restricts the number of rows returned by a query.

  • It can be combined with OFFSET to skip rows for pagination.

  • Using LIMIT with ORDER BY ensures meaningful selection of top or bottom records.

  • LIMIT can also be applied in UPDATE and DELETE statements for controlled changes.

  • Proper use of LIMIT improves performance, testing efficiency, and data presentation, especially with large datasets.

Practice Questions

  1. Retrieve the first 3 students from the students table.

  2. Retrieve 5 students starting from the 4th row using LIMIT with OFFSET.

  3. Fetch the 2 youngest students using ORDER BY and LIMIT.

  4. Fetch the 3 oldest students using ORDER BY and LIMIT.

  5. Display the first 10 students sorted alphabetically by name.

  6. Retrieve students for page 2 if each page shows 4 students (use LIMIT with OFFSET).

  7. Use LIMIT to fetch only 1 student from the city ‘Mumbai’.

  8. Update the ages of the 2 youngest students using LIMIT.

  9. Delete only 1 student from the table whose city is ‘Delhi’ using LIMIT.

  10. Fetch the next 3 students after skipping the first 5 rows when sorted by age.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top