From: Martijn Tonies
Date: November 23 2009 9:06pm
Subject: Re: LIMIT/OFFSET to paginate results
List-Archive: http://lists.mysql.com/mysql/219469
Message-Id: <007801ca6c80$c369e070$1401a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit



> Hello list :)
>
> I am developing an application that will show records in paginated
> documents, i.e. 10 records per page
>
> Lets supose this row structure
>
> MyTable
> ID(autoincrement)   SectionID   Name   Description
>
> The ID is automatic autoincrement for unique records, the SectionID is to
> separate items into different sections.
>
> If I query a particular SectionID rows it should return all those rows.
>
> If I use "LIMIT x,10" it should return 10 rows beginning at record #x, but
> my doubt is:
>
> Does the OFFSET x assumes its value to be #x number of consecutive rows, 
> or
> it is relative to the query results?
>
> For example
>
> ID   SID  name   description
> ------------------------------------------
> 01   01   nameA   descriptionA
> 02   02   nameB   descriptionB
> 03   01   nameC   descriptionC
> 04   02   nameD   descriptionD
> 05   02   nameE   descriptionE
> 06   01   nameF   descriptionF
> 07   02   nameG   descriptionG
> 08   01   nameH   descriptionH
> 09   02   nameI   descriptionI
> 10   02   nameJ   descriptionJ
> ------------------------------------------
>
> If I do
>   SELECT name from MyTable SID where SID='02' LIMIT 1,2 (offset 1, 2
> elements)
> it should return: nameB, nameD
>
> theni f I do
>   SELECT name from MyTable SID where SID='02' LIMIT 3,2 (offset 3, 2
> elements)
> it should return: nameE, nameG
>
> and if I do
>   SELECT name from MyTable SID where SID='02' LIMIT 3,2 (offset 5, 2
> elements)
> it should return: nameI, nameJ
>
> Is it correct? My doubt is if the OFFSET is relative to the SELECT results
> (to ignore the # first result rows) that match the condition SID='02' 
> only,
> to show 2 by 2 records (or N by N)...

Just by trying the above statement, you could have figured out the answer in
probably less time than the time it took you to write this message :-)


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com