Q

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.


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close