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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Weon-sun Lee"
Дата:
Сообщение: unsubscribe
Следующее
От: Pavel Mamin
Дата:
Сообщение: ...