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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002