LIMIT in SQL

The LIMIT clause tells SQL the maximum number of rows that should be returned.

SELECT *
FROM Students
LIMIT 5;

This returns up to five records from the Students table.

SELECT column_name
FROM table_name
LIMIT number;

The number specifies the maximum number of rows that should be returned.

SELECT *
FROM Students
LIMIT 10;

This returns a maximum of 10 rows from the Students table.

SELECT name, course
FROM Students
LIMIT 5;

This returns up to five records containing only the name and course columns.

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 fee.

SELECT *
FROM Students
WHERE city = 'Patna'
LIMIT 10;

This returns up to 10 students from Patna.

SELECT name, course, fee
FROM Students
WHERE city = 'Patna'
ORDER BY fee DESC
LIMIT 5;

This finds students from Patna, sorts them by fee from highest to lowest, and returns up to five records.

SELECT *
FROM Students
WHERE course IN ('Python', 'Java')
LIMIT 10;

This returns up to 10 students enrolled in Python or Java.

SELECT name, age
FROM Students
WHERE age BETWEEN 18 AND 30
LIMIT 10;

This returns up to 10 students whose age is between 18 and 30.

SELECT *
FROM Students
WHERE name LIKE 'A%'
LIMIT 5;

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

SELECT DISTINCT city
FROM Students
LIMIT 5;

This returns up to five unique city values.

LIMIT 1 is useful when you only need one record.

SELECT *
FROM Students
LIMIT 1;

This returns up to one record.

ORDER BY and LIMIT can be combined to find the highest value.

SELECT name, fee
FROM Students
ORDER BY fee DESC
LIMIT 1;

This returns a student record with the highest fee among the returned results.

SELECT name, fee
FROM Students
ORDER BY fee ASC
LIMIT 1;

This returns a student record with the lowest fee among the returned results.

In MySQL, LIMIT can be combined with an offset to skip a number of rows before returning results.

SELECT *
FROM Students
LIMIT 5 OFFSET 10;

This skips the first 10 rows and returns up to the next five rows.

MySQL also supports the shorter comma form:

SELECT *
FROM Students
LIMIT 10, 5;

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

LIMIT and OFFSET are commonly used to create pagination.

SELECT *
FROM Students
ORDER BY student_id
LIMIT 10 OFFSET 0;

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

SELECT *
FROM Students
ORDER BY student_id
LIMIT 10 OFFSET 10;

This skips the first 10 records and returns the next 10 records.

SELECT *
FROM Students
ORDER BY student_id
LIMIT 10 OFFSET 20;

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

SELECT course, COUNT(*) AS total_students
FROM Students
GROUP BY course
ORDER BY total_students DESC
LIMIT 5;

This returns up to five courses having the largest student counts.

LIMIT can be used with queries that return grouped results.

SELECT city, COUNT(*) AS total_students
FROM Students
GROUP BY city
ORDER BY total_students DESC
LIMIT 5;

This displays up to five cities with the highest number of students.

SELECT course, SUM(fee) AS total_fee
FROM Students
GROUP BY course
ORDER BY total_fee DESC
LIMIT 5;

This returns up to five courses with the highest total fee.

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

This returns up to five courses with the highest average fee.

LIMIT and OFFSET are useful when a report contains many records.

SELECT name, course, fee
FROM Students
ORDER BY name ASC
LIMIT 20 OFFSET 40;

This skips 40 records and returns the next 20 records.

SELECT name, email
FROM Students
WHERE email IS NOT NULL
ORDER BY name
LIMIT 10;

This returns up to 10 students who have an email value.

Suppose you want to display the students with the highest marks.

SELECT name, marks
FROM Students
ORDER BY marks DESC
LIMIT 10;

This returns the first 10 students after sorting by marks from highest to lowest.

A common mistake is using LIMIT before WHERE.

Incorrect:

SELECT *
FROM Students
LIMIT 5
WHERE city = 'Patna';

Correct:

SELECT *
FROM Students
WHERE city = 'Patna'
LIMIT 5;

WHERE comes before LIMIT in the SQL query structure.

LIMIT can reduce the number of rows returned to the application, which is particularly useful for large result sets.

SELECT name, course
FROM Students
ORDER BY student_id
LIMIT 20;

For large datasets, appropriate indexes and a well-designed ORDER BY can also be important for query performance.

Suppose an institute wants to display the five students with the highest fees who are studying Python or Java.

SELECT name, course, fee
FROM Students
WHERE course IN ('Python', 'Java')
ORDER BY fee DESC
LIMIT 5;

This filters the courses, sorts the students by fee, and returns up to five records.

SELECT name, age, course, city, fee
FROM Students
WHERE course IN ('Python', 'Java')
AND age BETWEEN 18 AND 30
AND city LIKE 'P%'
ORDER BY fee DESC, name ASC
LIMIT 10 OFFSET 20;

This query:

• Finds students studying Python or Java.
• Checks students between 18 and 30 years old.
• Finds cities beginning with P.
• Sorts the results by fee from highest to lowest.
• Uses name as a secondary ascending sort.
• Skips the first 20 records.
• Returns up to the next 10 records.