The confirmed answer is plain wrong.
Consider a subquery that generates a unique row index number.
ROWNUM in Oracle.
You need the subquery to create the unique record number for paging purposes (see below).
Consider the following example query:
SELECT T0.*, T1.* FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
SELECT DISTINCT T0.*, ROWNUM FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
WHERE (filter...) AND (ROWNUM > 10 AND ROWNUM < 20)
ORDER BY T1.Name DESC
The inner query is the exact same query but
DISTINCT on T0.
You can't put the
ROWNUM on the outer query since the
LEFT JOIN(s) could generate many more results.
If you could order the inner query (
T1.Name DESC) the generated
ROWNUM in the inner query would match.
Since you cannot use an
ORDER BY in the subquery the numbers wont match and will be useless.
Thank god for
ROW_NUMBER OVER (ORDER BY ...) which fixes this issue.
Although not supported by all DB engines.
One of the two methods,
LIMIT (does not require
ORDER) and the
ROW_NUMBER() OVER will cover most DB engines.
But still if you don't have one of these options, for example the
ROWNUM is your only option then a
ORDER BY on the subquery is a must!