in reply to Re^3: Should it be database independent?
in thread Should it be database independent?

It was a trick question. I know the rownum trick, but it's not the same.

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.

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.

  • Comment on Re^4: Should it be database independent?

Replies are listed 'Best First'.
Re^5: Should it be database independent?
by jplindstrom (Monsignor) on Mar 23, 2005 at 09:08 UTC
    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

[reply]
      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.

[reply]
[d/l]
[select]

In Section Meditations