Re: [SQL] Slashdot Query

Tom Lane wrote:

> 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.

Are yes I remember the issues now. I guess it would be nice if there was
some clause to specify which to elminate. Like SELECT DISTINCT
ELIMINATING LAST which then uses the ORDER BY clause to decide whether
to eliminate the first or last. But I can see that would be fiddly to
implement.

>
> 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)

I'll give it a go.

> 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

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris(dot)bitmead(at)bigfoot(dot)com