Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> 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?
>

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

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

David J.