Thread: Strange limit and offset behaviour....
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? Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
> > 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.
On Sat, 7 Feb 2009 15:49:49 -0600 "Adam Rich" <adam.r@sbcglobal.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
On Sat, Feb 7, 2009 at 9:57 PM, Bjørn T Johansen <btj@havleik.no> wrote: >> > SELECT * from table order by dato asc is the field 'dato' the same in both cases ? if so - you're goood just compare: select dato from table order by dato asc limit 10; with select dato from table order by dato asc limit 10 offset 0; -- GJ
On Sat, 7 Feb 2009 22:03:37 +0000 Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > On Sat, Feb 7, 2009 at 9:57 PM, Bjørn T Johansen <btj@havleik.no> wrote: > >> > SELECT * from table order by dato asc > > is the field 'dato' the same in both cases ? if so - you're goood > just compare: > > select dato from table order by dato asc limit 10; > with > select dato from table order by dato asc limit 10 offset 0; > > > yes, they are the same... And returns the same, now when I have created an index for the dato field... BTJ
On Sat, Feb 7, 2009 at 10:22 PM, Bjørn T Johansen <btj@havleik.no> wrote: > yes, they are the same... And returns the same, now when I have created an index for the dato field... so, ales in ordunung ;) you just have to choose different sorting key -- GJ