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