Thread: default ordering of query result - are they always guarantee
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
makethe query in the following form? Will this have performance impact on row with millions?
SELECT measurement_type, value, measurement_unit
FROM
measurement_1 ORDER BY measurement_id ASC
Thanks.
by ensuring measurement_1 result are ordered in ascending
Thanks and Regards
Yan Cheng CHEOK
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 makethe 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 makethe 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 explicitlymake 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.