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


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

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;


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

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)

Solarwinds
, ,
Previous Post
SQL SERVER – MySQL – PostgreSQL – How to Concat Strings?
Next Post
Developers – Top Ten Influential Movies for Developers – Add Your Favourite

Related Posts

10 Comments. Leave new

  • 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.

    Reply
  • isaiasnas (@_isaiasnas)
    December 24, 2014 8:54 pm

    to previous version sql2012

    DECLARE @OFFSET INT,@FETCH INT;
    SET @OFFSET=4; — change the value
    SET @FETCH=3; — change the value
    ;WITH T AS
    (
    SELECT T.* FROM (
    SELECT
    — $IDENTITY can be any column of your table
    ROW_NUMBER() OVER(ORDER BY T.$IDENTITY) [COUNT], T.*
    — change [TABLE] by your correct table
    FROM [TABLE] T
    ) T
    WHERE T.[COUNT] BETWEEN (@OFFSET+1) AND (@OFFSET+@FETCH)
    ) SELECT * FROM T;

    Reply
  • excellent….thanks man

    Reply
  • HI Sir,
    I was looking for the SQL Server 2008. And somewhere I found the query which can return the same result as you explained. (SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) as row FROM table_name
    ) a WHERE a.row > 10 and a.row <= 20

    But , here nested query is there, which will not be solution for the large data fetching. Does your query will do same thing internally?

    Reply
  • Anand Upadhaya
    March 16, 2016 8:35 pm

    Hi pinal.
    I have getting this error when we fire the first query.

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

    Reply
    • Please share output of Select @@version.

      This is a SQL Server 2012 feature, most likely you are running this on an earlier version of SQL Server.

      Reply
  • Vinay Tiwari
    May 5, 2016 12:24 pm

    Can i hide row Without delete and restore particular rows from the table in sql ?

    Reply

Leave a Reply

Menu