Re: Subquery to select max(date) value - Mailing list pgsql-general

>>>>> "Rich" == Rich Shepard <rshepard@appl-ecosys.com> writes:

 Rich> Tried this and did not do it correctly. Should there be two
 Rich> 'order by', one in the sub-query, the other in the top-level
 Rich> query?

Yes.

 Rich> This does not return the desired order:

 Rich> select p.person_id, p.lname, p.fname, p.direct_phone, p.active, o.org_name, sq.*
 Rich> from people as p
 Rich>      join organizations as o on p.org_id = o.org_id
 Rich>      cross join
 Rich>          lateral
 Rich>          (select a.next_contact
 Rich>          from activities as a
 Rich>              where a.person_id = p.person_id and
 Rich>              p.active='True' and
 Rich>              a.next_contact is not null
 Rich>          order by a.next_contact DESC
 Rich>          limit 1) sq
 Rich>          order by sq.next_contact DESC;

That query seems correct assuming you want the result in descending
order of next_contact. How did the actual result differ from your
expectation?

-- 
Andrew (irc:RhodiumToad)