SQL LIMIT Clause

The LIMIT clause in MySQL is used to restrict the number of rows returned by a query. Instead of retrieving the entire result set, you tell the database to return only a specific number of rows.

This is especially useful for large tables and is also commonly used to build pagination — showing results page by page in a web application.

Key difference: MySQL uses LIMIT at the end of the query. MS SQL Server uses TOP right after SELECT, or OFFSET...FETCH NEXT for pagination.

The Reference Table

Get the First N Rows

MySQL Query

-- Return only the first 3 rows
SELECT * FROM Students
LIMIT 3;

MS SQL Server Query

-- MS SQL Server uses TOP instead of LIMIT
SELECT TOP 3 * FROM Students;

LIMIT with ORDER BY

Combine LIMIT or TOP with ORDER BY to find the top or bottom records based on a value.

MySQL Query

-- Get the 3 youngest students
SELECT StudentName, Age FROM Students
ORDER BY Age ASC
LIMIT 3;

MS SQL Server Query

-- Get the 3 youngest students
SELECT TOP 3 StudentName, Age
FROM Students
ORDER BY Age ASC;

Pagination (LIMIT with OFFSET)

OFFSET tells the database to skip a certain number of rows before starting to return results. This is the foundation of pagination — showing results in chunks across multiple pages.

MySQL Query

-- Page 1: show rows 1-2
SELECT * FROM Students LIMIT 2 OFFSET 0;

-- Page 2: show rows 3-4
SELECT * FROM Students LIMIT 2 OFFSET 2;

-- Page 3: show row 5
SELECT * FROM Students LIMIT 2 OFFSET 4;

MS SQL Server Query

-- ORDER BY is required when using OFFSET...FETCH in MS SQL Server

-- Page 1: show rows 1-2
SELECT * FROM Students
ORDER BY StudentID
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY;

-- Page 2: show rows 3-4
SELECT * FROM Students
ORDER BY StudentID
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;

-- Page 3: show row 5
SELECT * FROM Students
ORDER BY StudentID
OFFSET 4 ROWS
FETCH NEXT 2 ROWS ONLY;

TOP with PERCENT (MS SQL Server Only)

MySQL Query

-- MySQL has no direct PERCENT equivalent; use a calculated LIMIT
SELECT * FROM Students
ORDER BY Age DESC
LIMIT 1;  -- Manually calculate how many rows = top 20%

MS SQL Server Query

-- Return the top 20% of rows based on age
SELECT TOP 20 PERCENT * FROM Students
ORDER BY Age DESC;

Key Points

• MySQL uses LIMIT n at the end of the query.
• MS SQL Server uses SELECT TOP n at the beginning of the query.
• For pagination, MySQL uses LIMIT n OFFSET x; MS SQL Server uses OFFSET x ROWS FETCH NEXT n ROWS ONLY.
• ORDER BY is required when using OFFSET...FETCH in MS SQL Server.
• Always combine row-limiting with ORDER BY to ensure meaningful and consistent results.