default ordering of query result - are they always guarantee

Le 19/05/2010 05:06, Yan Cheng CHEOK a �crit :

I have the following table :

CREATE TABLE measurement_1
(
measurement_id serial NOT NULL,
fk_unit_id int NOT NULL,
"value" double precision,
measurement_type text NOT NULL,
measurement_unit text NOT NULL
);

When I want to retrieve the query. By default, the query result are ordered in ascending order, by using measurement_id.

SELECT measurement_type, value, measurement_unit
FROM
measurement_1

This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly make the query in the following form?

An you're right. It's not guaranted. The only guaranted way is to use
ORDER BY your_column.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

In response to Guillaume Lelarge :

This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly make the query in the following form?

An you're right. It's not guaranted. The only guaranted way is to use
ORDER BY your_column.

ACK. One reason is the new feature 'synchronised scan' sind 8.3. It's a
big performance boost, but engendered unsorted results. (if there is not
the ORDER BY - statement)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

On Wed, May 19, 2010 at 1:38 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

In response to Guillaume Lelarge :

This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly make the query in the following form?

An you're right. It's not guaranted. The only guaranted way is to use
ORDER BY your_column.

ACK. One reason is the new feature 'synchronised scan' sind 8.3. It's a
big performance boost, but engendered unsorted results. (if there is not
the ORDER BY - statement)

Just for reference I once had three separate oracle dbas saying my pg
server was broken because group by came out in random order (due to
hash aggregates).

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Wednesday, May 19, 2010 4:15 AM
To: A. Kretschmer
Cc: pgsql-general@postgresql.org
Subject: Re: default ordering of query result - are they
always guarantee

...................................
...................................

Just for reference I once had three separate oracle dbas
saying my pg server was broken because group by came out in
random order (due to hash aggregates).

Must be not very good dbas :)
Oracle never guaranteed that "GROUP BY" returns sorted results.
It's just happened that in earlier Oracle versions they were sorted due
to algorithm being used for GROUP BY.
But again, Oracle never guaranteed it.

On Thu, May 20, 2010 at 8:11 AM, Igor Neyman <ineyman@perceptron.com> wrote:

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Wednesday, May 19, 2010 4:15 AM
To: A. Kretschmer
Cc: pgsql-general@postgresql.org
Subject: Re: default ordering of query result - are they
always guarantee

...................................
...................................

Just for reference I once had three separate oracle dbas
saying my pg server was broken because group by came out in
random order (due to hash aggregates).

Must be not very good dbas :)
Oracle never guaranteed that "GROUP BY" returns sorted results.
It's just happened that in earlier Oracle versions they were sorted due
to algorithm being used for GROUP BY.
But again, Oracle never guaranteed it.

Actually they were pretty good, but this was several years ago, and
they only had experience with Oracle 8, 9 was all shiny and new to
them. And they had the typical "If Oracle does X, it must be the way
things should be everywhere" Oracle DBA attitude.