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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.