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.klaver@aklaver.com