Paging through a result set with SQL

How can I retrieve only a specific number of records from the database using SQL? For example, consider there are 1000 records existing in the database for a specific criterion:

Select * from table_xyz where field1 = 'abcd'

I want to retrieve first 100 records, and then next 100 records and so on. I should be able to get first 1 to 100 records matched to the criterion. Next time when I run the query, I should get the next 100 records from the remaining 900 records.

The reason I am asking this is, my program is unable to handle the huge number of records returned. Memory Read errors are coming. It works fine when I query for few records.


    Requires Free Membership to View

    Login

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


This was first published in July 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top