Re: [HACKERS] SELECT ... LIMIT (trial implementation)

Oleg Bartunov wrote:

> On Sun, 18 Oct 1998, Tom Lane wrote:
>
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > What if someone wants the rows from 500 to the end.  Should we allow
> > > the syntax to be:
> > >          SELECT ... [LIMIT count] [OFFSET offset]
> > > LIMIT and OFFSET are independent.
> >
> > I like that syntax the best, but remember we are not inventing in
> > a green field here.  Isn't this a feature that already exists in
> > other DBMs?  We should probably copy their syntax, unless it's
> > truly spectacularly awful...
> >
> >            regards, tom lane
> >
>
> Mysql uses LIMIT [offset,] rows
> >From documentation:
>
>       LIMIT takes one or two numeric arguments. A single argument
>       represents the maximum number of rows to return in a result. If two
>       arguments are given the first argument is the offset to the first row to
>       return, while the second is the maximum number of rows to return in the
>       result.

    Simple change, just flip them in gram.y.

    And for the 500 to end:

        SELECT ... LIMIT 500, 0 (after flipped)

    The  0  has  the  same meaning as ALL. And that could also be
    added to the parser easily so one can say

        SELECT ... LIMIT 500, ALL

    too.

>
> What would be nice if somehow total number of rows could be returned.
> This is often needed for altavista-like application.
> Of course, I can do
> select count(*) from sometable ... LIMIT offset, rows
> and then
> select ... from sometable ... LIMIT offset, rows
> but this seems not elegant solution.

    Absolutely makes no sense  for  me.  As  said  in  the  other
    posting,  aggregates  do  the counting scan in a deeper level
    and thus cannot get limited. So if you invoke  an  aggregate,
    the whole scan is always done.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #