Re: [sqlite] how to use the LIMIT and OFFSET?

[Eric Scouten]

jack wu wrote:


i am trying to run some web search like query. the one
that returns a total number of results and only
presents the first 20, then allows user to click on
next to go to the next 20 etc.

has anyone done this before? i suppose i would use
LIMIT or OFFSET in the select statement, but the
offset only skips certain number of results before the
results i want to show, am i right?

so if i have 60 results, using OFFSET, i could skip
the first 20, and get the rest of 40, but i can't say,
give me the second 20.


First 20: SELECT ... LIMIT 20 OFFSET 0;

Next 20: SELECT ... LIMIT 20 OFFSET 20;

Next 20 after that: SELECT ... LIMIT 20 OFFSET 40;

etc.


"OFFSET n" basically means skip the first "n" rows that would have been 
returned otherwise.


"LIMIT n" says stop returning rows after the "n"th row, even if more 
rows would otherwise be available.


-Eric

Re: [sqlite] how to use the LIMIT and OFFSET?

[Kurt Welgehausen]

You should use SQL to get the data you want, then use
your host language to display the data. Those are 2
separate operations, and you'll be better off not mixing
them. If you're going to let your users go backward,
you're going to have to cache the data anyway.

Regards

[sqlite] how to use the LIMIT and OFFSET?

[jack wu]

i am trying to run some web search like query. the one
that returns a total number of results and only
presents the first 20, then allows user to click on
next to go to the next 20 etc.

has anyone done this before? i suppose i would use
LIMIT or OFFSET in the select statement, but the
offset only skips certain number of results before the
results i want to show, am i right?

so if i have 60 results, using OFFSET, i could skip
the first 20, and get the rest of 40, but i can't say,
give me the second 20.

thanks for you advice