Sql SKIP TAKE Pagination

Sql SKIP TAKE Pagination
Nikhil
Published on 2021-08-14 14:03:21

Limiting amount of results

ISO/ANSI SQL:

SELECT * FROM TableName FETCH FIRST 20 ROWS ONLY;

MySQL; PostgreSQL; SQLite:

SELECT * FROM TableName LIMIT 20;

Oracle:

SELECT Id,
Col1
FROM (SELECT Id,
Col1,row_number() over (order by Id) RowNumber
FROM TableName)WHERE RowNumber <= 20

SQL Server:

SELECT TOP 20 *
FROM dbo.[Sale]

Skipping then taking some results (Pagination)

ISO/ANSI SQL:

SELECT Id, Col1
FROM TableName
ORDER BY Id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

MySQL:

SELECT * FROM TableName LIMIT 20, 20; -- offset, limit
Oracle; SQL Server:
SELECT Id,
Col1
FROM (SELECT Id,
Col1,
row_number() over (order by Id) RowNumber
FROM TableName)
WHERE RowNumber BETWEEN 21 AND 40

PostgreSQL; SQLite:

SELECT * FROM TableName LIMIT 20 OFFSET 20;

Skipping some rows from the result

ISO/ANSI SQL:

SELECT Id, Col1
FROM TableName
ORDER BY Id
OFFSET 20 ROWS

MySQL:

SELECT * FROM TableName LIMIT 20, 42424242424242;

-- skips 20 for take use very large number that is more than rows in table

Oracle:

SELECT Id,
Col1
FROM (SELECT Id,
Col1,
row_number() over (order by Id) RowNumber
FROM TableName)
WHERE RowNumber > 20

PostgreSQL:

SELECT * FROM TableName OFFSET 20;

SQLite:

SELECT * FROM TableName LIMIT -1 OFFSET 20;

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website