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

On Thu, 28 Mar 2019, David G. Johnston wrote:

> Yes, if you join the result on an ordered subquery to anything you no
> longer have a guaranteed order for the combined relation.

David,

This makes sense to me.

> select ...
> from ...
> join ...
> cross join lateral ...
> -- now add an order by for the top-level query
> order by

Tried this and did not do it correctly. Should there be two 'order by', one
in the sub-query, the other in the top-level query? This does not return the
desired order:

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

Obviously, I'm still missing the implementation of your response.

Best regards,

Rich