Pagination Cheat Sheet > Querying with SQL

 

Pagination

E-commerce applications like Amazon or Flipkart hold millions of products. But, the user does not require all the available products every time s/he accesses the application. Infact, fetching all the products takes too long and consumes huge amount of data.

Using pagination, only a chunk of the data can be sent to the user based on their request. And, the next chunk of data can be fetched only when the user asks for it.

• We use
  LIMIT
  &
  OFFSET
  clauses to select a chunk of the results

Let's understand more about pagination concept using the following databse.

Database The database contains a

product

table that stores the data of products like name, category, price, brand and rating. You can check the schema and data of

product

table in the code playground.

LIMIT

LIMIT

clause is used to specify the

number of rows(n)

we would like to have in result.

Syntax

1

2

3

4

5

6

7

SELECT

column1,

column2,..

columnN

FROM

table_name

LIMIT n;

SQL

Example

Get the details of 2 top-rated products from the brand "Puma".

1

2

3

4

5

6

7

8

9

10

11

SELECT

name,

price,

rating

FROM

product

WHERE

brand = "Puma"

ORDER BY

rating DESC

LIMIT 2;

 

SQL

Expand

Output

name	price	rating
Black Shirt	600	4.8
Blue Shirt	1000	4.3

Try it Yourself!

• Get the 3 lowest priced products from the brand "Puma".
  Note

  If the limit value is greater than the total number of rows, then all rows will be retrieved.

OFFSET

OFFSET

clause is used to specify the position (from nth row) from where the chunk of the results are to be selected.

Syntax

1

2

3

4

5

6

7

SELECT

column1,

column2,..

columnN

FROM

table_name

OFFSET n;

SQL

Example

Get the details of 5 top-rated products, starting from 5th row.

1

2

3

4

5

6

7

8

9

10

SELECT

name,

price,

rating

FROM

product

ORDER BY

rating DESC

LIMIT 5

OFFSET 5;

SQL

Output

name	price	rating
Strawberry Cake	10	4.6
Bourbon Special	15	4.6
Realme Smart Band	3000	4.6
Harry Potter and the Goblet of Fire	431	4.6
Black Jeans	750	4.5

Possible Mistakes

• Using
  OFFSET
   before the
  LIMIT
  clause.

1

2

3

4

5

SELECT

*

FROM

product OFFSET 2

LIMIT 4;

SQL

1

Error: near "2": syntax error

SQL

• Using only
  OFFSET
  clause.

1

2

3

4

5

SELECT

*

FROM

product

OFFSET 2;

SQL

1

Error: near "2": syntax error

SQL

Note

OFFSET

clause should be placed after the

LIMIT

clause. Default

OFFSET

value is 0.

Try it Yourself!

• Get the details of 5 top-rated products, starting from 10th row.

View Schema

name	category	price	brand	rating
Blue Shirt	Clothing	750	Denim	3.8
Blue Jeans	Clothing	800	Puma	3.6
Black Jeans	Clothing	750	Denim	4.5
Blue Shirt	Clothing	1000	Puma	4.3
Chocolate Cake	Food	25	Britannia	3.7
Strawberry Cake	Food	60	Cadbury	4.1
Chocolate Cake	Food	60	Cadbury	2.5
Strawberry Cake	Food	10	Britannia	4.6
Smart Watch	Gadgets	17000	Apple	4.9
Smart Cam	Gadgets	2600	Realme	4.7
Smart TV	Gadgets	40000	Sony	4
Bourbon Small	Food	10	Britannia	3.9
Bourbon Special	Food	15	Britannia	4.6
Bourbon With Extra Cookies	Food	30	Britannia	4.4
White Shirt	Clothing	700	Denim	4.3
Black Shirt	Clothing	600	Puma	4.8
Black T-Shirt	Clothing	600	Roadster	4.2
White T-Shirt	Clothing	700	Levi's	4
Blue T-Shirt	Clothing	600	Nike	4.7
Realme Smart Band	Gadgets	3000	Realme	4.6
Raw Cashew	Food	370	Absa	3.9
Cashew Nuts	Food	550	Upcrop	4.3
Chocolate Cashew	Food	670	Urban Platter	3.5
Potato Chips India’s Magic Masala	Food	42	Lay's	4.4
Banana Chips	Food	550	Calicut Kerala	4.3
Potato Chips Cream & onion	Food	63	Lay's	4.5
Potato Chips Classic Salted	Food	45	Lay's	4
Harry Potter and the Philosopher's Stone	Novel	222		4.7
Harry Potter and the Chamber of Secrets	Novel	343		4.4
Harry Potter and the Prisoner of Azkaban	Novel	284		4.2
Harry Potter and the Goblet of Fire	Novel	431		4.6
OnePlus 6T	Smartphone	32990	OnePlus	4.5
Redmi K20	Smartphone	24999	Redmi	4.1