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
