On 8/19/21 8:39 AM, Rich Shepard wrote:
> 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;
> ----------
Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com