LIMIT and OFFSET clause in BigQuery

Limit and offset are good companion with SELECT (). It can limit the number of rows to return in a query. Optionally includes the ability to skip over rows.

LIMIT: Limits the number of rows to produce. LIMIT 0 returns 0 rows.

OFFSET: Skips a specific number of rows before applying LIMIT.

The fictitious dataset called “Ivys_shop.csv” can be downloaded here.

For example, I want to retrieve only 5 rows from the table.

SELECT *  FROM `instruction-415216.examples.ivys_shop` 
LIMIT  5

Row	Name	Category	Price	Quantity	Sold	Purchase_Price
1	Kiwi	Fruit	8.86	99	10	2.7
2	Kiwi	Fruit	4.61	41	35	2.34
3	Kiwi	Fruit	0.85	74	57	2.56
4	Kiwi	Fruit	2.7	39	22	0.95
5	Kiwi	Fruit	7.52	86	80	0.6

For example, I want to skip the top 10 records and retrieve 6 rows.

SELECT *  FROM `instruction-415216.examples.ivys_shop` 
LIMIT 6 OFFSET 10

Row	Name	Category	Price	Quantity	Sold	Purchase_Price
1	Mango	Fruit	7.91	47	19	1.82
2	Mango	Fruit	4.84	49	9	1.4
3	Peach	Fruit	1.02	90	71	1.76
4	Peach	Fruit	4.33	96	7	3.58
5	Peach	Fruit	4.1	25	25	1.98
6	Peach	Fruit	0.72	78	44	0.7