Search Postgresql Archives

Re: Subquery to select max(date) value

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux