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