Re: Help with a subselect inside a view

On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote:
> > CREATE VIEW cl (id, class_time, instructor)
> > AS
> > SELECT DISTINCT ON(class.id)
> > class.id, class.class_time, person.first_name
> > FROM class, instructors, person
> > WHERE instructors.person = person.id
> > AND class.id = instructors.class;
>
> This is allowed because the code automatically adds "ORDER BY class.id"
> within the view (as you would see if you examined the view with \d).

I see that now. Might be helpful for the docs to say that for folks
like me.

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

Thanks,

--
Bill Moseley
moseley(at)hank(dot)org