Re: [HACKERS] distinct. Is this the correct behaviour?

On 21-Oct-99 Tom Lane wrote:
> Vince Vielhaber <vev(at)michvhf(dot)com> writes:
>
> If we do "select distinct x from foo order by y" on this data, then the
> order of the result depends on which of the two tuples with x=1 happens
> to get chosen by the Unique filter. This is not good.

What seems logical to me tho is that it should first select all of the
x cols that are equal, put them in y order, then pick the first one for
the distinct. At least this is the behaviour that I'm looking for; perhaps
I'm going to need to make a more complex call. I also wonder how other
RDBMS are handling it... I'll hafta see what sybase does (if I remember
*and* get a chance).

> SQL92 gets around this by allowing ORDER BY only on columns of the
> targetlist, so that you are not allowed to specify this query in the
> first place.

I can understand the reason, yet also fail to understand.

> I think it is useful to allow ORDER BY on hidden columns, but maybe we
> need to forbid it when DISTINCT is present. If we do that then the
> implementation of nodeUnique is OK as it stands, and the bug is that
> the parser accepts an invalid query.
>
> This is pretty closely related to the semantic problems of DISTINCT ON,
> once you see that the trouble is having columns in the query that aren't
> being used for (or aren't supposed to be used for) the DISTINCT check.

Ok, well what I'm trying to do is write a web-based discussion forum. I
wanted to list the subjects in any particular forum, but also want them
to be in the order in which they were first posted. So if I have 10
comments on one subject which first started last month and the subject
begun with a 'z', and another that was started today with the subject
beginning with an 'A', I want the end result to be:

zebras have stripes
Always cross at the light

as opposed to 10 lines about the zebras and only one on Always. It
seems elementary, but at the same time it seems complex. Must mean
it's time for bed.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)com flame-mail: /dev/null
# include <std/disclaimers.h> Have you seen http://www.pop4.net?
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================