MySQL LIMIT Clause

MySQL Tutorial

MySQL LIMIT Clause

The LIMIT clause in MySQL is used to restrict the number of rows returned by a SELECT query.

    It’s essential for pagination, top-N queries, and performance optimization.

 What is LIMIT?

  • Controls how many rows are returned

  • Often combined with ORDER BY

  • Very useful for large tables

 LIMIT affects only the result set, not the data stored.

 Basic Syntax

Example

  • Returns first 5 rows

LIMIT with OFFSET

Used to skip rows before returning results.

Syntax

Example

  •  Skips first 5 rows
  • Returns next 5 rows (rows 6–10)

LIMIT + ORDER BY (Very Important)

To get top or bottom records, always use ORDER BY.

Top 3 Highest Salaries

Lowest 5 Marks

LIMIT for Pagination

Common formula:

Example: Page 3 (10 records per page)

 LIMIT with WHERE Clause

The WHERE clause filters rows based on a condition, and LIMIT restricts how many of those filtered rows are returned.
WHERE is applied first, then LIMIT is applied to the result.
This combination is commonly used to control data size and avoid large result sets.
  • WHERE filters rows before LIMIT is applying.

LIMIT with GROUP BY

GROUP BY groups rows and performs aggregation before LIMIT is applied.
LIMIT restricts the number of grouped results, not individual rows.
It is usually used with ORDER BY to control which groups appear first.
  •  Top 3 departments by employee count

 LIMIT vs TOP (Interview)

LIMIT and TOP both restrict the number of rows returned, but they belong to different SQL systems.
LIMIT is used in MySQL, PostgreSQL, and SQLite, while TOP is used in SQL Server.
LIMIT is written at the end of the query, whereas TOP is written immediately after SELECT.

LIMIT Example


 

TOP Example


 

Both queries return 10 rows, but the syntax depends on the database.

  • MySQL → LIMIT

  • SQL Server → TOP

  • PostgreSQL → LIMIT

Common Mistakes

  •  Using LIMIT without ORDER BY (results can be unpredictable)
  •  Confusing offset and count
  •  Expecting LIMIT to delete data
  •  Using negative values

Interview Questions & MCQs (Very Important)

Q1. What does LIMIT do?

A) Deletes rows
B) Updates rows
C) Restricts number of rows returned
D) Sorts rows

Answer: C

Q2. Which query returns first 10 rows?

SELECT * FROM table LIMIT 10;

A) Correct
B) Incorrect

Answer: A

Q3. What does LIMIT 5, 10 mean?

A) First 5 rows
B) Rows 5 to 10
C) Skip 5, return next 10
D) Return 10, skip 5

Answer: C

Q4. LIMIT is best using with which clause?

A) WHERE
B) GROUP BY
C) ORDER BY
D) HAVING

Answer: C

Q5. Is LIMIT supporting in standard SQL?

A) Yes
B) No (DB-specific)

Answer: B

Q6. Which is correct for pagination?

LIMIT (page-1)*size, size

A) Correct
B) Incorrect

Answer: A

Real-Life Use Cases

  •  Pagination in web apps
  •  Top-N reports
  •  Sampling large datasets
  •  Faster previews of data

 Summary

  • LIMIT restricts number of rows returned

  • Supports OFFSET

  • Best used with ORDER BY

  • Essential for pagination

  • DB-specific but very common

  • Important for SQL exams & interviews

You may also like...