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
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 |