Limit Clause¶

If we do not want to return every row of a SELECT Query, we can simply limit the number of returned values with the - you guessed it - LIMIT Clause.

This is especially useful in large databases, where you do not need to load every single row.

The syntax looks as follows:

SELECT
    column_list
FROM
    table
ORDER BY 
  column 1
LIMIT 
  number_of_rows;

Note: the ORDER BY clause is optional. It has to be placed before the LIMIT clause.

Let's try to return only the first 3 rows of our STUDENT Table:

ROLL_NO	NAME	AGE	BRANCH	CITY
10001	Kate	22	CSE	Mumbai
10002	Richard	21	ECE	Delhi
10003	Rick	33	ME	Chennai
10004	Peter	26	CSE	Delhi
10005	Peter	22	CSE	Noida
10006	Mark	22	ECE	Jaipur
10007	Brad	23	ME	Rampur

The query

SELECT * FROM STUDENT LIMIT 3;

returns the following table:

ROLL_NO	NAME	AGE	BRANCH	CITY
10001	Kate	22	CSE	Mumbai
10002	Richard	21	ECE	Delhi
10003	Rick	33	ME	Chennai

Offset the Limit

If we want to get the 2 rows starting after the 3rd row, we can use the OFFSET keyword as follows:

SELECT
    column_list
FROM
    table
LIMIT 
  number_of_rows 
  OFFSET number_of_offset_rows;

Applied to our example this will look as follows:

SELECT
    *
FROM
    STUDENT
LIMIT 2 OFFSET 3;

will result in:

ROLL_NO	NAME	AGE	BRANCH	CITY
10004	Peter	26	CSE	Delhi
10005	Peter	22	CSE	Noida