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.

CodePudding user response：

You can use distinct on twice, once on(id, value1, value2) in a derived table to get rows when (value1, value2) changes, then in the outer query on(id) to get the row where there is the last change on (value1, value2):

select distinct on(id) * from
(select distinct on(id, value1, value2) id, end_date, value1, value2
from table1
order by id, value1, value2, end_date) t
order by id, end_date desc;

Fiddle

CodePudding user response：

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 *, row_number() over(partition by id order by end_date desc) rn
       from tbl 
     ) t
     where rn = 1
   ) t2 on t1.value1 = t2.value1 and t1.value2 = t2.value2 
) t
where rn = 1