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 (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – MySQL – LIMIT and OFFSET – Skip and Return Only Next Few Rows – Paging Solution

  1. Hi Pinal,
    Thanks for sharing such paging trick. But while i execute the same query on my side in one of our databases it gives the error. Below mentioned is the query and the error.

    SELECT * FROM dbo.userhd
    ORDER BY id
    OFFSET 20 ROWS
    FETCH NEXT 10 ROWS ONLY;

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘offset’.
    Msg 153, Level 15, State 2, Line 4
    Invalid usage of the option NEXT in the FETCH statement.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s