Pagination - SQL Server Vs MySQL‏

Continuing my series on how same things can be done differently in SQL Server and MySQL, this week we will see how Pagination works in SQL Server and MySQL. Pagination is a method of showing result in small batches if the results return a large set of data.

Consider the following set of data

create table testing
(
    sales_id int,
    product_id char(7),
    sales_date datetime,
    sales_amount decimal(12,2)
)
 
insert into testing(sales_id,product_id,sales_date,sales_amount)
select 1,'PR00001','2001-01-01',1200.00 union all
select 2,'PR00002','2003-01-21',3000.50 union all
select 3,'PR00002','2003-01-21',2500.00 union all
select 4,'PR00001','2002-02-15',1000.00 union all
select 5,'PR00003','2005-12-19',3000.50 union all
select 6,'PR00003','2006-11-01',8000.00 union all
select 7,'PR00004','2007-04-22',350.00 union all
select 8,'PR00004','2007-04-22',590.00 union all
select 9,'PR00004','2007-04-22',590.00 union all
select 10,'PR00001','2008-05-27',4800.50

Let us assume that you want to return the first 5 rows from the results ordered by sales_date

SQL Server

Version 2012 onwards you can use OFFSET and FETCH NEXT Clauses

select * from testing
order by sales_date
offset 0 rows
fetch next 5 rows only

The above code orders the results by ascending order of sales_date and fetches 5 rows. The next 5 rows can be returned using the following code

select * from testing
order by sales_date
offset 5 rows
fetch next 5 rows only

The FETCH clause skips the first 5 rows and returns the next five rows


MySQL

The same functionality can be done in MySQL using the LIMIT clause

select * from testing
order by sales_date
LIMIT 0,5

The above code returns first five rows in ascending order of sales_date. To get next five rows, use the following code

select * from testing
order by sales_date
LIMIT 5,10

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan