Re: Help with a subselect inside a view

Hi David,

On Thu, Aug 25, 2005 at 01:22:02AM -0700, David Fetter wrote:
> This sounds like a case for PostgreSQL's nifty DISTINCT ON functionality.
>
> 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.

Perhaps I not understanding the wording above? Or is Postgresql
adding in the order automatically?

My original VIEWS with duplicates:

DROP VIEW cl;
CREATE VIEW cl (id, class_time, instructor)
AS
SELECT class.id, class.class_time, person.first_name
FROM class, instructors, person
WHERE instructors.person = person.id
AND class.id = instructors.class;

select * from cl where id = 555;
id | class_time | instructor
-----+------------------------+------------
555 | 2005-09-30 09:00:00-07 | Cheryl
555 | 2005-09-30 09:00:00-07 | Bob
(2 rows)

And with DISTINCT ON():

DROP VIEW cl;
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;

select * from cl where id = 555;
id | class_time | instructor
-----+------------------------+------------
555 | 2005-09-30 09:00:00-07 | Cheryl
(1 row)

Here where the leftmost ORDER BY doesn't match the DISTINCT ON, which I thought
was not possible:

select * from cl where class_time > now() order by instructor limit 3;
id | class_time | instructor
-----+------------------------+------------
544 | 2005-08-31 09:00:00-07 | Cheryl
555 | 2005-09-30 09:00:00-07 | Cheryl
737 | 2005-08-30 09:00:00-07 | Cynthia

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