SQL LIMIT / TOP Clause — Controlling Output Rows

The school wants the first two students on a ranked mark list, with a predictable answer when marks tie.

A row-limit clause restricts how many result rows are returned. LIMIT, TOP and FETCH syntax differ across database engines.

Order the qualifying result before applying the requested row count. MySQL, PostgreSQL and SQLite support LIMIT in common query forms; SQL Server commonly uses TOP or OFFSET/FETCH. A limit without an ordering rule does not define which qualifying rows should be considered first.

Related concepts: Sort Results and Tune SQL Performance.

SQL · portable concepts; preserved examples identify their engine

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;

SQL · portable concepts; preserved examples identify their engine

SELECT TOP number column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE student_marks (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_marks VALUES
(1, 'Aarav Sharma', '10A', 'Maths', 85),
(2, 'Diya Iyer', '9B', 'Maths', 90),
(3, 'Rohit Menon', '10A', 'Maths', 78),
(4, 'Sneha Patil', '8C', 'Maths', 88),
(5, 'Mehul Agarwal', '9B', 'Maths', 92);

SQL · portable concepts; preserved examples identify their engine

Let’s list the top 3 students in Maths based on marks:

SELECT name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC
LIMIT 3;

name            | marks
----------------+-------
Mehul Agarwal  | 92
Diya Iyer      | 90
Sneha Patil    | 88

SQL · portable concepts; preserved examples identify their engine

SELECT TOP 3 name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC;

SQL · portable concepts; preserved examples identify their engine

Suppose you want to skip the top 2 students and fetch the next 2. Use OFFSET:

SELECT name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC
LIMIT 2 OFFSET 2;

name          | marks
---------------+-------
Sneha Patil   | 88
Aarav Sharma  | 85

SQL · portable concepts; preserved examples identify their engine

Let’s get the highest marks in Maths per class. You’ll need GROUP BY and a subquery for this:

SELECT class, MAX(marks) AS top_score
FROM student_marks
WHERE subject = 'Maths'
GROUP BY class
ORDER BY top_score DESC
LIMIT 3;

• Always use ORDER BY with LIMIT or TOP to ensure predictable results.
• Use OFFSET for pagination in apps or dashboards.
• Test in your specific SQL dialect — some use LIMIT, others use TOP.

LIMIT 2 does not mean the two highest marks unless ORDER BY expresses that ranking. Use a unique tie-breaker for consistent pagination. Large OFFSET values can require substantial work; limiting output does not guarantee cheap execution.

Find the top two students by marks with roll number settling ties.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, marks FROM practice_students ORDER BY marks DESC, roll_no LIMIT 2;

The ordering rule ranks the 85-mark students first and then the limit retains two rows. The remaining student is still stored.

A row-limit clause restricts how many result rows are returned. LIMIT, TOP and FETCH syntax differ across database engines.

The ordering rule ranks the 85-mark students first and then the limit retains two rows. The remaining student is still stored.

Continue your understanding with Sort Results and Tune SQL Performance.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.