Re: Strange limit and offset behaviour....

On Sat, 7 Feb 2009 15:49:49 -0600
"Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> wrote:

> >
> > I have the following sql:
> >
> > SELECT * from table order by dato asc limit 20 offset 0
> >
> >
> > This gives me different rows than the 20 first rows when running the
> > following sql:
> >
> > SELECT * from table order by dato asc
> >
> >
> > Shouldn't the 20 first rows in the second sql statment be the same 20
> > rows that is returned in the first statement
> > or am I missing something?
> >
>
> Not necessarily. In your example query, if "dato" was not a unique
> column, and there were some duplicates, the "top 20" values is not
> a defined set. Adding the offset clause might cause a different
> query plan, resulting in a different ordering of the duplicate values.
>
>
I found out that if I created an index on the dato field, then I got the same 20 rows... Does that make sense or is
it just a coincedense?

BTJ