Re: Help with a subselect inside a view

[Bill Moseley <moseley@xxxxxxxx>]

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@xxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster