Requires Free Membership to View
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 first published in October 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation