LIMIT/OFFSET to paginate results

--000e0cd5d15cc2286f04790ce48d
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0cd5d15cc2286f04790ce48d--

Re: LIMIT/OFFSET to paginate results

> 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: LIMIT/OFFSET to paginate results

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org