How to get earliest date from time parameter without including data that have data older than time parameter in SQL?

Question

I'm struggling for some time with kind of painfull problem.

What I want to achieve is to find the blue circle on the image below. It is to find date where some name occurs first in the database but it is the furthest date out of all possible occurences (furthest in terms of distance between start date parameter and all possible gaps between start date and data)

I know how to find the date right after start date. To do this I'm using that query.

SELECT *
FROM animals
WHERE date(date) > '2020-01-01'
ORDER BY date ASC
LIMIT 1 ;

But I don't know how to exclude the data that occurs before 2020-01-01 in this query.

I would like to do it in form of SQL query.

My SQL table looks like this:

date	name	value
2022-02-01	dog	45.5
2022-02-01	cat	2.5
2022-02-01	snake	2.5
2022-01-31	dog	42.5
2022-01-31	cat	3.4
2022-01-31	snake	43.2
2022-01-30	dog	43.2
2022-01-30	cat	43.2
2022-01-30	snake	43.2
2022-01-29	dog	43.2
2022-01-29	snake	43.2
2022-01-28	dog	43.2

And let's say that I'm looking for date after 2022-01-28. Now gap between start_date and first occurence of dog is 0 because dog has data for 2022-01-28. For snake it is 1 day (2022-01-29) and for cat it's 2 (2022-01-30)

So my result should be 2022-01-30 (2 days from start_date for cat) because it is the furthest from the start_date

Answer 1

Search for distinct on and you'll see several explanations on how to solve this.

This should work (I changed date field to event_date to keep it less confusing)

with earliest_dates as (
 select distinct on (name) name, event_date 
 from animals
 where event_date > '2020-01-01'
 order by name, event_date
)
select  max(event_date) - min(event_date) as days
from earliest_dates;

sql fiddle link for test setup http://sqlfiddle.com/#!15/67389/4/0