distinct + order by

From: t-ishii(at)sra(dot)co(dot)jp (Tatsuo Ishii)
To: hackers(at)postgreSQL(dot)org
Subject: distinct + order by
Date: 1998-11-07 23:51:22
Message-ID: 199811072353.IAA29308@meshsv26.tk.mesh.ad.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

6.4 allows to use ORDER BY with a column that does not appear
in a target list. This is great. However, following result seems
strange to me. Sometimes DISTINCT does not remove duplication.
Is this normal behavior?

create table dtest (i text, j date, k int);
CREATE
insert into dtest values ('a', '1998/12/1', 1);
INSERT 215243 1
insert into dtest values ('a', '1998/12/2', 2);
INSERT 215244 1
insert into dtest values ('a', '1998/12/3', 3);
INSERT 215245 1
select * from dtest;
i| j|k
-+----------+-
a|12-01-1998|1
a|12-02-1998|2
a|12-03-1998|3
(3 rows)

select distinct i from dtest;
i
-
a
(1 row)

select distinct i from dtest order by j;
i
-
a
a
a
(3 rows)

select distinct i from dtest order by k;
i
-
a
a
a
(3 rows)

--
Tatsuo Ishii
t-ishii(at)sra(dot)co(dot)jp

Browse pgsql-hackers by date

  From Date Subject
Next Message Terry Mackintosh 1998-11-08 01:30:19 regression tests
Previous Message Constantin Teodorescu 1998-11-07 20:20:05 Bugs on opening views defined with spaces or upon table names with spaces !!!