Re: Help with a subselect inside a view

Bill Moseley <moseley(at)hank(dot)org> writes:
>> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

> The DISTINCT ON expression(s) must match the leftmost ORDER BY
> expression(s). The ORDER BY clause will normally contain additional
> expression(s) that determine the desired precedence of rows within
> each DISTINCT ON group.

> I read that and thought it wasn't a drop-in replacement for my code
> due to the leftmost ORDER BY requirement. But, it seems to work even
> if that requirement is not met.

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

regards, tom lane