On further checking, rownum doesn't respect order by and you have to use a nested select if you want the top N of an ordered set.

Taken from http://c2.com/cgi/wiki?ThinkSqlAsConstraintSatisfaction, about halfway down the page. Further more (and I haven't looked for the reference), limit works with nested queries correctly where rownum doesn't always work. It's a neat almost-the-same, but it's not.

I'm not really interested in arguing for or against Oracle, but I'm interested in how you think the Oracle version differs semantically from what you're used to in MySQL. "getting rows N through M of a result set" sounds a lot like the LIMIT clause regardless of how clumsy the actual syntax is. So I guess I just don't get your point.

Personally I think syntactic sugar matters a lot, so I wouldn't mind if they extended their SQL dialect to support this since it's obviously something a lot of people have a need for.

(The issue about rowid and "order by" etc is a known issue but it's not a problem with the solution presented at Ask Tom.)

/J

The main difference is that using ROWNUM in the general case requires a HAVING clause, which requires a GROUP BY clause. Doing that may require the use of aggregation functions in the SELECT clause, which is, I think you'd agree, a fundamental difference.

The fact that ROWNUM (and ROWID, but that's not the topic here) is assigned before the ORDER BY clause is executed is another major issue.

SELECT foo
  FROM bar
 ORDER BY 1
 LIMIT 5, 10
[download]

versus

SELECT foo
  FROM (
        SELECT foo
          FROM bar
         ORDER BY 1
       )
 GROUP BY 1
 HAVING ROWNUM >= 5
    AND ROWNUM <= 10
[download]

I know which I'd rather work with ...

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.