SELECT DISTINCT and ORDER BY
От | Joerg Eriskat |
---|---|
Тема | SELECT DISTINCT and ORDER BY |
Дата | |
Msg-id | Pine.LNX.4.10.9909151051140.14967-100000@gcf20m.icf.med.uni-muenchen.de обсуждение исходный текст |
Ответы |
Re: [SQL] SELECT DISTINCT and ORDER BY
|
Список | 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
В списке pgsql-sql по дате отправления: