List:General Discussion« Previous MessageNext Message »
From:Miguel Cardenas Date:November 23 2009 5:09pm
Subject:LIMIT/OFFSET to paginate results
View as plain text  
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