Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA02071 for ; Fri, 4 Jun 1999 11:30:10 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by trends.net (8.8.8/8.8.8) with ESMTP id KAA29715 for ; Fri, 4 Jun 1999 10:23:41 -0400 (EDT) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA16849; Fri, 4 Jun 1999 10:21:53 -0400 (EDT) To: Chris Bitmead cc: pgsql-sql@hub.org Subject: Re: [SQL] Slashdot Query In-reply-to: Your message of Fri, 04 Jun 1999 13:18:53 +1000 <3757459C.F0E63B5E@bigfoot.com> Date: Fri, 04 Jun 1999 10:21:53 -0400 Message-ID: <16847.928506113@sss.pgh.pa.us> From: Tom Lane Chris Bitmead writes: > SELECT DISTINCT category.oid, category.title, category.image FROM story, > category* WHERE story.category = category.oid AND story.approved ORDER > BY datetime DESC LIMIT 5; > The trouble is it doesn't return distinct results, but rather it returns > duplicates. This is a known problem --- SELECT DISTINCT implies sorting by the fields that are being "distincted" on, since the actual duplicate- elimination relies on a uniq(1)-like adjacent-duplicates filter. Forcing a sort by a different field breaks the duplicate eliminator. It is not real clear what the correct behavior is, which is why nothing's been done about it; we've gone round on the issue a couple of times (see the pghackers archives). My own thought is that the query as given above should be illegal, since there is no unique value of datetime to go with a "distinct" set of oid, title, image. You might consider using GROUP BY rather than DISTINCT if you want to order the results in a particular way, say SELECT oid, title, image ... GROUP BY oid, title, image ORDER BY min(datetime) where you use an aggregate like min or max to resolve the ambiguity about which datetime to associate with a particular group... regards, tom lane