On 9/3/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> After doing some SELECT operation on a TABLE, say we get 100 result
> items out of 1000 records, Is there any way where I can iterate
> through this result set. i.e., Get N items out of 100, say get
> previous 10, get next 10, etc,
>
> This should be done without creating a temporary table or virtual
> tables. As there is overhead of space and time.
There is always the overhead of space or time, as it's impossible to
know what the results are unless you actually calculate them. To get
row 100, the query must find rows 1-99 first.
The LIMIT and OFFSET clauses of the SELECT statement can be used to
get parts of a query without storing anything. This is the worst case
of time overhead though, since the entire query must be run every
time.
An approach that works for many people is to store markers for use in
relative queries. This requires a column with unique, ordered values,
and that you run the query using it as an ORDER BY. An INTEGER
PRIMARY KEY AUTOINCREMENT column is useful for this; let's assume you
have one named ID. If you retrieve rows 101-110, you can store the
IDs for row #101 (let's say this is ID 234), and row #110 (ID 280).
When you need to retrieve the previous 10 rows, you get them backwards
using a query such as:
SELECT * FROM table
WHERE id < 234
ORDER BY id DESC
LIMIT 10;
This avoids having to retrieve rows for the entire result set when
changing "pages" sequentially.
You can imagine other schemes that involve storing all the IDs for the
query result set so that you can jump to them at random, etc.
> If it is possible to use VIEWs then how can I? As I think VIEWs are
> little better than temp table or virtual table. Please correct me if
> I'm wrong.
Better for doing what? A view isn't going to help with paging result sets.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
