Re: Subquery to select max(date) value

>>>>> "Rich" == Rich Shepard <rshepard(at)appl-ecosys(dot)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)