Difference between revisions of "Get the 11th to the 20th rows of the cia table - by population."

SELECT name, population FROM bbc
 ORDER BY population DESC LIMIT 10 OFFSET 10

SELECT TOP 10 x.name, x.population FROM (
 SELECT TOP 20 name, population FROM bbc
  ORDER BY population DESC) x
 ORDER BY x.population ASC

SELECT name, population FROM bbc
 ORDER BY population DESC LIMIT 10 OFFSET 10

SELECT name, population FROM (
 SELECT name, population, rownum n FROM (
   SELECT name,population FROM bbc
                        ORDER BY population DESC)
 )
WHERE n BETWEEN 11 AND 20

SELECT TOP 10 x.name, x.population FROM (
 SELECT TOP 20 name, population FROM bbc
  ORDER BY population DESC) x
 ORDER BY x.population ASC

SELECT * FROM bbc
 ORDER BY population DESC
 LIMIT 11, 10

You cannot usefully use rownum in the WHERE clause - however you can refer to it in an outer query.

Chris Sinclair of QuarterStaff proposes:

SELECT name, population FROM (
SELECT name, population, rownum n FROM (
  SELECT name,population FROM bbc
                       ORDER BY population DESC)
 where rownum <=20
)
WHERE n >= 11

While this makes an insignificant difference to the performance of the query on the cia dataset, if the innermost select does not require a full table scan then the Oracle optimiser will stop it as soon as twenty rows have been returned rather than generating rownum records for the entire query and then discarding the excess.