|
There are really two related questions here --
paging through a result set, or
limiting the number of rows returned by a query.
If you are returning results to an application where
speed of execution is important,
and your application has scripting capabilities,
like a Web interface built with ASP or PHP or ColdFusion,
for example, then you should perhaps not
repetitively query the database. Rather, retrieve all
pertinent rows in one query and cache them in the
application, using scripting to page through the
results. However, if you already have memory problems,
this may not be an option for you.
To page through a result set using SQL,
you will need to select or limit which
rows you want. Check your database syntax for
a keyword like LIMIT (MySQL and PostgreSQL)
or TOP (Microsoft SQL/Server and Access)
or ROWNUM (Oracle -- although using this
with ORDER BY can be tricky). These keywords
allow you to restrict the number of rows returned.
Note that you must have an ORDER BY clause,
because otherwise, the "first" 100 rows doesn't make sense.
Retrieving the first 100 rows with the TOP keyword is easy --
select top 100
id
, field1
, fieldn
from table_xyz
where field1 = 'abcd'
order by id
The LIMIT keyword, on the other hand, is a bit more flexible --
select id
, field1
, fieldn
from table_xyz
where field1 = 'abcd'
order by id
limit 0, 100
Here LIMIT 0,100 means "start with the
first row and retrieve 100" (the first row has offset 0).
With LIMIT, it's easy to see how you would get the next 100 --
select id
, field1
, fieldn
from table_xyz
where field1 = 'abcd'
order by id
limit 100, 100
If you are using TOP, you can "pass"
the value of the sort field of 100th row from the
first query into a subquery in the second query
when you go back for the second 100 rows --
select top 100
id
, field1
, fieldn
from table_xyz
where field1 = 'abcd'
and id > value
order by id
Otherwise, you could use a convoluted subquery
(caution: not tested) --
select top 100
id
, field1
, fieldn
from table_xyz
where field1 = 'abcd'
and id > ( select max(id)
from ( select top 100 id
from table_xyz
where field1 = 'abcd'
order by id )
)
order by id
In all of these cases, the second and subsequent queries have to
be modified each time before they are executed, to specify
the appropriate group of records. For More Information
|
