Re: Selecting table row with latest date

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(dot)klaver(at)aklaver(dot)com