WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL LIMIT

Previous Next

MySQL LIMIT Clause

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