From: | Joerg Eriskat <Eriskat(at)icf(dot)med(dot)uni-muenchen(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SELECT DISTINCT and ORDER BY |
Date: | 1999-09-15 08:53:10 |
Message-ID: | Pine.LNX.4.10.9909151051140.14967-100000@gcf20m.icf.med.uni-muenchen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I'm not sure it is a bug, so I'm asking the SQL gurus. I think
there is a inconsistency with SELECT DISTINCT queries when used in
combination with ORDER BY and column alias names (sorry for the rather
long example):
create table t1 (f1 int4);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2); -- double
create table t2 (f1 int4);
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);
-- this query seems ok:
select distinct a.f1 as x1, b.f1 as x2
into t3
from t1 a, t2 b;
-- this query does not select *distinct*:
select distinct a.f1 as x1, b.f1 as x2
into t4
from t1 a, t2 b
order by a.f1, b.f1;
-- but this one does:
select distinct a.f1 as x1, b.f1 as x2
into t5
from t1 a, t2 b
order by x1, x2;
select count(*) from t3;
select count(*) from t4;
select count(*) from t5;
[ output skipped ... ]
select count(*) from t3;
count
-----
6
(1 row)
select count(*) from t4;
count
-----
9
(1 row)
select count(*) from t5;
count
-----
6
(1 row)
This was with Postgres 6.5.1 and RedHat 6.0 on an i486. My apologies if
this is just my plain bad SQL or if it is a known issue (but I did not
find it in the history file for v. 6.5.2).
Greetings,
Joerg
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Mamin | 1999-09-15 10:11:50 | |
Previous Message | Weon-sun Lee | 1999-09-15 06:49:16 | unsubscribe |