in reply to Re^2: Ranking position in a SQL index
in thread Ranking position in a SQL index

It is a MySQL extension, but an damn useful one and it's one of the things I consider when determining whether to use MySQL or Oracle for a given project.

Oracle does have something similar, but it's harder to do.

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.

Replies are listed 'Best First'.
Re^4: Ranking position in a SQL index (vairants of limit:sybase)
by demerphq (Chancellor) on Oct 05, 2004 at 17:42 UTC

    Sybase does it via a variable called 'rowcount'. So you say 

    set rowcount 100
select * from foo

    [download]

    But there is no way to do the equivelent of the skip feature of limit to the best of my knowledge. You'd have to manage it through ids and a where I think. I agree that 'limit' is one of a small set of things I like more about MySQL than Sybase. At least insofar as the MySQL that PM uses is the comparison I much prefer Sybase. :-)


    ---
    demerphq

      First they ignore you, then they laugh at you, then they fight you, then you win.
      -- Gandhi

      Flux8


[reply]
[d/l]
      The problem is that to implement "limit" in a truly relational system you really have to internally fetch all the rows before the first row specified in the limit, and then start returning rows. So you're going to do a lot of IOs if you have something like 
      select ... from big_table limit 1000000, 1000010

      [download]
      And of course you should always include an ORDER BY clause as there is no guarantee that the result set will be int the "right" order.

      On the other hand if you use a WHERE clause the server can limit the IO to the correct pages of data...

      I have implemented versions of LIMIT by using a temporary table with an identity column, something like: 

      set rowcount 10010
select pk, id = syb_identity(10) into #tmp from big_table where some_c
+ondition
select b.* from big_table b, #tmp t 
 where t.pk = b.pk
   and t.id >= 10000
set rowcount 0

      [download]
      Obviously a lot of IO if you need to find rows located fairly deep into the result set, but acceptable for a pagination system where the user usually only looks at the first few pages...

      Michael

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom