From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Bitmead <chris.bitmead@bigfoot.com>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Slashdot Query
Date: Fri, 04 Jun 1999 10:21:53 -0400
Message-ID: <16847.928506113@sss.pgh.pa.us> (raw)
In-Reply-To: <3757459C.F0E63B5E@bigfoot.com>
Chris Bitmead <chris.bitmead@bigfoot.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, chris.bitmead@bigfoot.com, pgsql-sql@hub.org
Subject: Re: [SQL] Slashdot Query
In-Reply-To: <16847.928506113@sss.pgh.pa.us>
References: <3757459C.F0E63B5E@bigfoot.com> <16847.928506113@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox