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