Re: Help with a subselect inside a view

Bill Moseley <moseley(at)hank(dot)org> writes:
> On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote:
>> It's fairly pointless though, because as the manual notes, you can't get
>> any well-defined behavior without additional ORDER BY columns to
>> prioritize the rows within class.id groups. As is, you're getting
>> random choices of class_time and first_name within the groups.
>> (Though maybe in this application, you don't care.)

> I'm not sure I follow what you are saying. I understand that I have
> no control over which "first_name" I end up with (and I don't really
> care), but class_time is a column in the "class" table which I'm using
> DISTINCT ON on, so that should be unique as well. So I assume you
> meant random choice of first_name, not class_time.

Sorry, I meant that the query by itself doesn't guarantee anything about
which values you will get. If you know a-priori that there is only one
value of class_time per class id, then of course you don't care which
row it's selected from. But from the point of view of this query,
you're getting an unspecified one of the possible values.

In most of the applications I've seen for DISTINCT ON, people *do* care.
For instance, if you wanted to further constrain what you were getting,
you might wish that the returned first_name were the alphabetically
first among the class's instructors. You could get that with
SELECT DISTINCT ON(class.id)
...
ORDER BY class.id, person.first_name;

regards, tom lane