Re: Refining query statement

On 1/15/19 9:47 AM, Thomas Kellerer wrote:
> Rich Shepard schrieb am 15.01.2019 um 16:39:
>>   Working with my sales/client management system using psql I have a select
>> statement to identify contacts to be made. This statement works:
>>
>> select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact)
>> from Contacts as C, Organizations as O, Activities as A
>> where C.org_id = O.org_id and C.contact_id = A.contact_id and
>>       A.next_contact <= 'today' and A.next_contact > '2018-12-31' and
>>       A.next_contact is not null;
>>
>> but would benefit from tweaking. When I have had multiple contacts with
>> someone I want only the most recent one displayed, not all, and they should
>> be no more ancient than a defined period (e.g., a month).
>>
>>   I want to learn how to make this query cleaner and more flexible. When I
>> write the UI for this I want to be able to specify a data range in addition
>> to a fixed 'today'. Pointers on what to read will be very helpful.
> 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.
>
> "cleaner" is always subjective, but I find explicit JOIN operators a lot cleaner than the old implicit joins.
>
> 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.
>
> 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;

And I've never liked this method (though I'm old and crotchety)....

--
Angular momentum makes the world go 'round.