Hannu Krosing wrote:
> As SQL queries are all one-time things you can't be "consistent".
> It's like being able to grab the same set of socks from a bag and
> then trying to devise a strategy for getting them in same order
> without sorting them (i.e. possible but ridiculous)
>
> If you need them in some order, you use ORDER BY, if you don't need
> any order you omit ORDER BY.
>
> > My predudices are caused by what I use PostgreSQL for, which is
> > more favourable to the latter.
>
> Whats wrong with using ORDER BY ?
Only that it's non intuitive that ORDER BY should change the actual
results of a series of LIMIT queries, not just the order. If there are
100 records, and I do 10x LIMIT 10,offset queries one might expect to
get all 100 records. And currently you do (barring something unusual
like a vacuum at an inopportune moment that drastically changes
statistics).
> I can't imagine a set of queries that need to be consistent
> _almost_ all the time, but without any order.
>
> If you really need that kind of behaviour, the right decision is
>to select the rows into a work table that has an additional column
>for preserving order and then do the limit queries from that
>table.
Impractical for stateless web based stuff where keeping state around is
painful if not impossible.
I'm just playing devils advocate here. Changing this is probably not
going to hurt me, I just think it could confuse a lot of people.
> But in that case it is often faster to have an index on said column
> and to do
> WHERE ID BETWEEN OFFSET AND OFFSET+LIMIT
> ORDER BY ID
> than to use LIMIT, more so for large offsets.
--
Chris Bitmead
mailto:chris@bitmead.com