Re^5: Should it be database independent?

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.)

Comment on Re^5: Should it be database independent?

Replies are listed 'Best First'.
Re^6: Should it be database independent? by dragonchild (Archbishop) on Mar 23, 2005 at 13:28 UTC
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]

Replies are listed 'Best First'.

Re^6: Should it be database independent?
by dragonchild (Archbishop) on Mar 23, 2005 at 13:28 UTC

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]

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]