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:
From this previous query, the query that I need to obtain is the following:
So once again, I need the row where value1, value2 in min(end_date) = max(end_date), grouped by id.
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