Bill Moseley <moseley@xxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
