Received: from tech.com.au (IDENT:root@techpt.lnk.telstra.net [139.130.75.122]) by hub.org (8.9.3/8.9.1) with ESMTP id FAA69894 for ; Sun, 25 Apr 1999 05:23:46 -0400 (EDT) (envelope-from chris.bitmead@bigfoot.com) Received: from bigfoot.com (chris@localhost [127.0.0.1]) by tech.com.au (8.8.7/8.8.7) with ESMTP id TAA21148 for ; Sun, 25 Apr 1999 19:23:23 +1000 Sender: chris@tech.com.au Message-ID: <3722DF08.D8E19160@bigfoot.com> Date: Sun, 25 Apr 1999 09:23:20 +0000 From: Chris Bitmead X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Confusion about DISTINCT. References: <371F2ED6.29FD9251@scigen.co.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I'm a bit confused about DISTINCT. Firstly, what does DISTINCT mean when you don't use "ON". Like this... SELECT DISTINCT category.image FROM story, category* WHERE story.category = category.oid ORDER BY datetime DESC; The DISTINCT here has no effect on the output other than changing the ordering. There are duplicates! 2nd question: Why does the following query result in duplicates even though I use DISTINCT ON? If I change the ORDER BY to be on image, then there are no duplicates but that isn't what I want. I want the time to be the sort order because I want the X most recent images but only unique ones. Is this a bug? It certainly seems wierd that DISTINCT would return duplicates. Why should it be up to the user to order the output with reference to the DISTINCT clause? Shouldn't the database take care of that? ... SELECT DISTINCT ON image category.image FROM story, category* WHERE story.category = category.oid ORDER BY datetime DESC; image -------------------- /icon/canon.gif /icon/arca-swiss.gif /icon/canon.gif /icon/hasselblad.gif /icon/nikon.gif /icon/olympus.gif (6 rows) -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com