Re: Selecting table row with latest date

On Thu, 19 Aug 2021, Tom Lane wrote:

> The best way is usually like
>    select * from mytable order by contact_date desc limit 1;
> If you have an index on contact_date this should work very well indeed.

tom,

I added an index on contact_date and the query returned only one row. Huh!
Not what I expected.

This is the script I need to fine-tune (and I've forgotten the role of sq
since someone suggested it a few years ago):

----------
/* This query selects all whose next_contact date is today or earlier; no nulls.
    This version should select the most recent contact_date by person_nbr,
    order by person_nbr and next_contact date. STILL NEEDS WORK.
*/

select p.person_nbr, p.lname, p.fname, p.direct_phone, p.cell_phone, o.company_name, sq.*
from people as p
      join companies as o on p.company_nbr = o.company_nbr
      cross join
          lateral
          (select *
          from contacts as a
              where a.person_nbr = p.person_nbr and
              a.next_contact <= current_date and
              a.next_contact is not null
          order by person_nbr, a.next_contact ASC
          ) sq
          order by sq.next_contact ASC;
----------

Rich