List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:November 23 2009 9:07pm
Subject:Re: LIMIT/OFFSET to paginate results
View as plain text  
The order the records are returned is not guaranteed unless you  
specify an ORDER BY. You could run the same query multiple times and  
the order the records are returned could be different each time.  
Although this is rarely the case, especially with caching enabled.
Always do an ORDER BY with pagination, and make sure what you are  
ordering by is unique. This is simple to do, just add the unique ID  
field as the last order by field. Then you will always get  
"consecutive" rows.

Brent


On Nov 23, 2009, at 12:09 PM, Miguel Cardenas wrote:

> 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)...
>
> Thanks for your help

Thread
LIMIT/OFFSET to paginate resultsMiguel Cardenas23 Nov
  • Re: LIMIT/OFFSET to paginate resultsMartijn Tonies23 Nov
  • Re: LIMIT/OFFSET to paginate resultsBrent Baisley23 Nov