On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> wrote:
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
You need the ORDER BY in the outer join. (And I don't think the one inside the lateral join is doing you any good). Try:
...
a.next_contact is not null
limit 1) sq
order by sq.next_contact DESC;limit 1) sq
Cheers,
Ken
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
