Paging through a result set without TOP or LIMIT

This question relates to Paging through a result set with SQL (16 July 2002). What I want to do is a select like...

the one in the question above and page through the result set. In the answer, you state how to do that on a MySQL server, and mention Microsoft SQL and Oracle. But what about Sybase? I've been tearing my hair out trying to find a solution.

We can adapt the generic Top N query to this task. I would not use the generic method when TOP or LIMIT is available, but you're right, the previous answer is incomplete without this.

Using the same table and column names, the top 100 ids are given by:

select id
     , field1
     , fieldn 
  from table_xyz X 
 where ( select count(*) 
           from table_xyz  
          where id > X.id ) < 100
 order by id desc

The subquery is correlated, which means that it will be evaluated for each row of the outer query. The subquery says "count the number of rows that have an id that is greater than this id." Note that the sort order is descending, so we are looking for ids that are greater, i.e. higher up in the result set. If that number is less than 100, then this row must be one of the top 100. Simple, eh? Unfortunately, it runs quite slowly. Furthermore, it takes ties into consideration, which is good, but this means that the number of rows returned isn't always going to be exactly 100 -- there will be extra rows if there are ties extending across the 100th place.

Next, we need the second set of 100:

select id
     , field1
     , fieldn 
  from table_xyz X 
 where ( select count(*) 
           from table_xyz  
          where id > X.id ) between 100 and 199
 order by id desc

See the pattern? Note that the same caveat applies about ties that extend across 200th place.

This was last published in October 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.