From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Refining query statement |
Date: | 2019-01-15 16:34:32 |
Message-ID: | alpine.LNX.2.20.1901150829570.5845@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2019-01-15 16:37:28 | Re: Refining query statement |
Previous Message | Rich Shepard | 2019-01-15 16:26:24 | Re: Refining query statement |