Received: from servus.gsf.de (servus.gsf.de [146.107.101.11]) by hub.org (8.9.2/8.9.1) with ESMTP id HAA43665 for ; Thu, 28 Jan 1999 07:46:20 -0500 (EST) (envelope-from tmetz@gsf.de) Received: from gsf.de (pc2408.gsf.de [146.107.135.65]) by servus.gsf.de (8.8.6 (PHNE_14041)/8.8.6) with ESMTP id NAA02535 for ; Thu, 28 Jan 1999 13:51:19 +0100 (MET) Message-ID: <36B05F8F.DFB94ED8@gsf.de> Date: Thu, 28 Jan 1999 14:01:03 +0100 From: Thomas Metz X-Mailer: Mozilla 4.05 [de] (WinNT; I) MIME-Version: 1.0 To: pgsql-sql@hub.org Subject: SELECT DISTINCT ON ... ORDER BY ... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have the following problem: Assuming the table TEST as follows: ID NAME ----------------- 1 Alex 2 Oliver 1 Thomas 2 Fenella SELECT DISTINCT ON id id, name FROM test; produces: ID NAME ----------------- 1 Alex 2 Oliver SELECT DISTINCT ON id, name FROM test ORDER BY name; produces: ID NAME ----------------- 1 Alex 2 Fenella 1 Thomas I would have expected only two rows in both queries. I don't care which names actually appear in the output as long as they are sorted, but there should no longer be duplicate id's. If the table is as follows: ID NAME ----------------- 2 Oliver 2 Alex 1 Thomas 1 Fenella SELECT DISTINCT ON id id, name FROM test; produces: ID NAME ----------------- 1 Thomas 2 Oliver SELECT DISTINCT ON id, name FROM test ORDER BY name; produces: ID NAME ----------------- 2 Alex 1 Fenella 2 Oliver 1 Thomas What seems to happen is that from the sorted table, duplicate id's are eliminated only if they are grouped. If there is no explicit sorting I assume the DISTINCT performs an implicit sorting on id and then eliminates records correctly. Is that the correct behaviour? Is there another (simple) way of getting the results I want? I am still using version 6.3 tm -- Thomas Metz GSF - National Research Center for Environment and Health Institute of Mammalian Genetics