How to obtain row for minimum date for a value that is equal to the value in maximum date, per group?

I am trying to obtain row for a query.. where row value1, value2 in minimum end_date is equal to the max end_date, per group of id’s.

My current query only obtains the row value1, value2 from the max end_date in the query result:

select a.id, a.end_date, a.value1, a.value2
from table1 as a
inner join (
    select id, max(end_date) as end_date
    from database1
    group by id
) as b 
on a.id = b.id and a.end_date = b.end_date
order by id, end_date

This result obtains the most recent record, but I’m looking to obtain the row where value1 & value2 in min(end_date = max(end_date), grouped by id.

Here is an example query detailing what I want to obtain:

id	end_date	value1	value2
AAPL	02/12/22	2	1
AAPL	02/13/22	2	1
AAPL	02/14/22	3	2
AAPL	02/15/22	3	2
MSFT	03/01/22	2	5
MSFT	03/02/22	4	5
MSFT	03/03/22	4	5
MSFT	03/04/22	4	5

From this previous query, the query that I need to obtain is the following:

id	end_date	value1	value2
AAPL	02/14/22	3	2
MSFT	03/02/22	4	5

So once again, I need the row where value1, value2 in min(end_date) = max(end_date), grouped by id.

Answer

You can do it with row_number()

select id, end_date, value1, value2
from (
   select t1.*, row_number() over(partition by t1.id order by t1.end_date) rn
   from tbl t1
   join (
     select id, end_date, value1, value2
     from (
       select t.*, row_number() over(partition by id order by end_date desc) rn
       from tbl t
     ) t
     where rn = 1
   ) t2 on t1.id = t2.id and t1.value1 = t2.value1 and t1.value2 = t2.value2 
) t
where rn = 1

db<>fiddle