Re: Subquery to select max(date) value

On Fri, 15 Feb 2019, Andrew Gierth wrote:

> select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.*
>  from people as p
>       join organizations as o on p.organization_id=o.id   -- OR WHATEVER
>       cross join
>         lateral (select a.next_contact
>                    from activities as a
>                   where a.person_id=p.person_id         --VERY IMPORTANT
>                     and a.next_contact > '2018-12-31'
>                     and a.next_contact <= 'today'
>                     and a.next_contact is not null
>                   order by a.next_contact DESC
>                   limit 1) sq;

After working with this query I modified it slightly to return only the
next_contact date:

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;

It works wellm, but the row order is not that of a.next_contact. In fact,
there seems to be no order in the returned set. The next_contact column is
in the lateral sub-query. Does this make a difference? I've no idea how to
modify the query so that returned rows are in decreasing next_contact order.

Best regards,

Rich