Thread: ORDER BY results

Hi,

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?

For instance, say I have a table as follows:

create table example {id serial primary key, stuff int};

Now suppose I the table is populated as follows:

ID                STUFF
1                  10
2                  11
3                  11
4                  12

Now if i try SELECT ID,STUFF FROM EXAMPLE ORDER BY STUFF; I might expect

ID                STUFF
1                  10
2                  11
3                  11
4                  12

but possibly could get

ID                STUFF
1                  10
3                  11
2                  11
4                  12

... Depending on the implementation. Whichever result I get, can I
always expect the output in the same way? Or should I ensure this by
sorting by the ID aswell

e.g. SELECT ID,STUFF FROM EXAMPLE ORDER BY STUFF, ID; - which would
always give the same order.

In case you are interested, the application for this problem is the
presentation of partial results using LIMIT (as per search results
20,21...30) - for instance could the following present different results
to the same client...

SELECT ID,STUFF FROM EXAMPLE WHERE STUFF=11 ORDER BY STUFF LIMIT 0, 1;

Could give the following on one transaction
2                  11
And the following on another transaction
3                  11

I am not bothered about which way the results are presented and I am
aware that the order would be implementaion specific, but obviously the
results presented should always be the same for my app.

Regards,

Howard Cole
www.selestial.com

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

Howard Cole <howardnews@selestial.com> writes:
> 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?

No.  You need to add more columns to the ORDER BY clause to ensure a
unique ordering, if you want to be sure of that.

(For sufficiently small values of "always", such as if the table hasn't
been updated in between, the results may appear stable ... but it's not
guaranteed.)

            regards, tom lane