MySQL LIMIT Clause: Definition, Syntax, and Examples

The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records.

Syntax:

SELECT column_names FROM table_name
LIMIT number_of_rows;

Demo Database

Below is a selection from the "Students" table in the school_db database:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

1. Using `LIMIT` with `SELECT` (Fetching a Fixed Number of Rows)

The following SQL statement selects the first five records from the "Students" table:

Example

SELECT * FROM students

LIMIT 5;

Try it yourself

2. Using `LIMIT` with `OFFSET` (Pagination)

Skips the first 3 rows and fetches the next 4.

The SQL query below says "return only 4 records, start on record 3 (OFFSET 3)":

Example

SELECT * FROM students

LIMIT 4 OFFSET 3;

Try it yourself

Alternative using shorthand syntax:

Example

SELECT * FROM students

LIMIT 3, 4;

Try it yourself

3. `LIMIT` with `ORDER BY`

The following SQL statement sorts the students by score before returning the first three records from the selection:

Example

SELECT * FROM students

ORDER BY score

LIMIT 3;

Try it yourself

4. `LIMIT` with `where`

The following SQL statement selects the first five records from the "Students" table, where the city is "Mumbai":

Example

SELECT * FROM students

WHERE city = 'Mumbai'

LIMIT 5;

Try it yourself

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.