Get the 11th to the 20th rows of the bbc 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
We select the bottom 10 of the top 20 - we end up with the list in the wrong order - this can be fixed with another nested select.
You cannot usefully use rownum in the WHERE clause - however you can refer to it in an outer query. People generally need guarantees that they receive good grades when order
custom term paper or purchase custom academic papers. Alas, not every academic writing service is able to give those guarantees.
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.
