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)
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.
I get the same error as well. Does this only work with certain versions of SQL Server?
Rahul: This is a SQL Server 2012 feature, you are probably running this query on an earlier version of SQL Server.
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;
excellent….thanks man
your welcome budi.
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?
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.
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.
Can i hide row Without delete and restore particular rows from the table in sql ?