On Tue, 15 Jan 2019, Thomas Kellerer wrote:
With regards to "cleaner": the first thing to do is to remove the
parentheses around the column list. In Postgres "(a,b,c)" creates a single
column with an anonymous record type (that contains three fields), rather
than selecting three columns. In other DBMS those parentheses are simply
useless.
Thomas,
I should have seen that myself. Thanks for pointing out the obvious to me.
"cleaner" is always subjective, but I find explicit JOIN operators a lot
cleaner than the old implicit joins.
This does make sense; I need to refresh my knowledge of JOIN operators and
will do so.
The condition "A.next_contact is not null" is actually no necessary
because you already have a condition on that column, so NULL values won't
be returned anyway.
True that.
To get the "most recent one" in Postgres, DISTINCT ON () is usually the
best way to do it: So we end up with something like this:
select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact
from Contacts as C
join Organizations as O on C.org_id = O.org_id
join Activities as A on C.contact_id = A.contact_id
where A.next_contact <= 'today'
and A.next_contact > '2018-12-31'
order by c.contact_id, a.next_contact DESC;
This insight really helps. Now I know how to approach other queries.
Many thanks,
Rich
