Thread

Limit and Order by stuff

sszabo@bigpanda.com — 2000-02-14T15:55:14Z

Actually, even currently, limit and order a non unique
order by can skip results if the table is being modified.
Even if no new rows are entered, as long as a row
on the border of the limit has been modified, you can
get indeterminate results.

acroyear=> create table test1 (a int, b varchar(10), c int);
CREATE
acroyear=> insert into test1 values (1, 'a', 1);
INSERT 748222 1
acroyear=>  insert into test1 values (2, 'a', 1);
INSERT 748223 1
acroyear=> insert into test1 values (3, 'a', 1);
INSERT 748224 1
acroyear=> insert into test1 values (4, 'a', 1);
INSERT 748225 1
acroyear=> insert into test1 values (4, 'b', 2);
INSERT 748226 1
acroyear=>  insert into test1 values (5, 'a', 1);
INSERT 748227 1
acroyear=> insert into test1 values (6, 'a', 1);
INSERT 748228 1
acroyear=> insert into test1 values (7, 'a', 1);
INSERT 748229 1
acroyear=> select a,b from test1 order by a limit 4;
a|b
-+-
1|a
2|a
3|a
4|a
(4 rows)

acroyear=> update test1 set c=3 where a=4 and b='a';
UPDATE 1
acroyear=> select a,b from test1 order by a offset 4 limit 4;
a|b
-+-
4|a
5|a
6|a
7|a
(4 rows)