Re: Subquery to select max(date) value

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Subquery to select max(date) value
Date: 2019-02-15 21:07:28
Message-ID: alpine.LNX.2.20.1902151303280.31662@salmo.appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, 15 Feb 2019, Andrew Gierth wrote:

> LATERAL (SELECT ...) is syntactically like (SELECT ...) in that it comes
> _after_ a "," in the from-clause or after a [LEFT] JOIN keyword. Don't
> think of LATERAL as being a type of join, think of it as qualifying the
> (SELECT ...) that follows.

Andrew,

Thank you. Now I understand the difference.

> Rich> from activities as a
> Rich> where a.next_contact is not null and a.next_contact <= 'today' and
> Rich> a.next_contact > '2018-12-31'
>
> You'd want a condition here that references the "people" table; the whole
> point of LATERAL is that it opens up the scope of column references in the
> subquery to include those tables which are to its left in the from-clause.

And here I got it backwards, thinking the subquery could reference the
columns in the people table from the initial select.

> Rich> order by person_id,next_contact);
>
> and I'm guessing you want that ordered by next_contact alone, possibly
> with LIMIT 1 to get just the nearest following next_contact time.

That's true. With 'discrete on' only the most recent next_contact date is
returned.

More work over the weekend on this now I have a better understanding of
lateral.

Thanks again,

Rich

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2019-02-15 21:14:17 Re: Channel binding not supported using scram-sha-256 passwords
Previous Message Andrew Gierth 2019-02-15 20:53:07 Re: Subquery to select max(date) value