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@hank.org