| Oracle | ||
| Specific to Oracle | ||
|
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 cia
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.
| ||