Re: ORDER BY results

On Thu, Sep 22, 2005 at 02:19:00PM +0100, Howard Cole wrote:
> I have a query which presents results sorted using the SQL "ORDER BY...
> LIMIT". Now my question is... if the column that the order refers to
> has some repeated data, will the order of results always be the same?

Not necessarily -- if you want a certain order then you'll need a
more specific ORDER BY clause. Here's an example:

CREATE TABLE example (
id integer PRIMARY KEY,
stuff integer NOT NULL
);

INSERT INTO example (id, stuff) VALUES (1, 10);
INSERT INTO example (id, stuff) VALUES (2, 11);
INSERT INTO example (id, stuff) VALUES (3, 11);
INSERT INTO example (id, stuff) VALUES (4, 12);

SELECT id, stuff FROM example ORDER BY stuff;
id | stuff
----+-------
1 | 10
2 | 11
3 | 11
4 | 12
(4 rows)

UPDATE example SET stuff = 11 WHERE id = 2;

SELECT id, stuff FROM example ORDER BY stuff;
id | stuff
----+-------
1 | 10
3 | 11
2 | 11
4 | 12
(4 rows)

Notice that the second query returns rows in a different order than
the first query. In this simple example I'd guess that the order
is related to the tuples' physical locations on disk: when we updated
the row with id = 2 the database made another version of that row
and stored it in a later position (you can see tuples' physical
locations by selecting the ctid system column). If we ran VACUUM
and did the update again, the tuple would probably go back to its
original location (read up on MVCC to see how this works).

--
Michael Fuhr