SQL SERVER – MySQL – LIMIT and OFFSET – Skip and Return Only Next Few Rows – Paging Solution

Working with MySQL and SQL Server together has its own sets of fun. Here is one example how you can implement paging in MySQL or SQL Server. Both of them have LIMIT and OFFSET mechanism. You can Skip few rows and return few rows from the sorted result set with the help of OFFSET keywords. Let us see how we can do that quickly in the both of the database. We will use AdventureWorks sample database for SQL Server and sakila sample database for MySQL.

SQL Server

USE AdventureWorks2012
GO
-- Skip 20 rows and return only the next 10 rows from the sorted result set
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

MySQL

USE sakila;
-- Skip 20 rows and return only the next 10 rows from the sorted result set
SELECT *
FROM actor
ORDER BY actor_id
LIMIT 10
OFFSET 20;

If you are interested more about how paging works, here are few of my earlier blog posts on the same subject.

Reference: Pinal Dave (https://blog.sqlauthority.com)