select DISTINCT not ordering the returned rows

From: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: select DISTINCT not ordering the returned rows
Date: 2011-03-02 20:35:01
Message-ID: 2608.96821.qm@web120119.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Everyone,

I would like to ask for your help finding a temporary solution for my problem.
I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows.

The following script is a simplification of my real case:

create table tmp_1 (field1 integer, field2 integer);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 1029);
insert into tmp_1 values (1, 1101);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 1029);
insert into tmp_1 values (13, 1101);
analyze tmp_1;
SELECT distinct field2 FROM tmp_1 WHERE field1 = 13;

The result in postgres 8.3 is as follows:
3
4
1029
1101
And it stays the same no matter what the physical order of the records is in the table. I can do random update and I get the same results. It looks like the result is ordered by the distinct fields...

The result in postgres 9.0 is as follows:
3
4
1101
1029
not ordered by the distinct fields nor physical order...

I am wondering if there is a temporary solution (updates, indexes, ...) to order the result by field1 without changing the statement...

Thank you in advance,
Ioana Danes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ioana Danes 2011-03-02 20:39:28 Re: select DISTINCT not ordering the returned rows
Previous Message James B. Byrne 2011-03-02 20:11:30 How to approach dynamic status reporting