September 28, 2012

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

result1
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

result2


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

2 Responses to "Pagination - SQL Server Vs MySQL‏"
  1. Anonymous said...
    September 28, 2012 at 9:00 PM

    Very good article

  2. Anonymous said...
    October 23, 2012 at 6:12 AM

    The pagination with limit and offset is a very limited example. I understand it is meant to show an example of how you can do it but this generally wouldn't be the best way to execute pagination. Check out these slides Yahoo provided on using limit and ids with as an alternate way of implementing it in MySQL.

    http://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions