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:

Rich> I've not before run 'explain' on a query. Would that be
Rich> appropriate here?

Yes.

Andrew,

I'll learn how to use it.

The problem here is that you have no join conditions at all, so the
result set of this query is massive. And you've duplicated many tables
inside the subquery which is not necessary or appropriate.

Got it now.

select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.*
 from people as p
      join organizations as o on p.organization_id=o.id   -- OR WHATEVER
      cross join
        lateral (select a.next_contact
                   from activities as a
                  where a.person_id=p.person_id         --VERY IMPORTANT
                    and a.next_contact > '2018-12-31'
                    and a.next_contact <= 'today'
                    and a.next_contact is not null
                  order by a.next_contact DESC
                  limit 1) sq;

Ordering by DESC with a limit 1 is used to get the max next_contact
value rather than the smallest; this is similar to max(), but makes it
trivial to also access the other columns of the _same_ activities row
which is being selected.

This puts everything in perspective and is a very valuable lesson for me as
this application has many queries of this type. You've put together all that
I've read in the manual, on this mail list thread, and on web pages. I
really appreciate your patient guidance.

Best regards,

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