By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table tbl (Name text, Status text, SubStatus text, EndDate date);
insert into tbl values
('AAA', 'Active', 'High', null),
('AAA', 'Inactive', 'Intermediate', '20171201'),
('BBB', 'Inactive', 'High', '20171231'),
('BBB', 'Inactive', 'Intermediate', '20171201'),
('BBB', 'Inactive', 'Low', '20171214'),
('CCC', 'Inactive', 'Intermediate', '20170911'),
('CCC', 'Inactive', 'Low', '20170921'),
('DDD', 'Inactive', 'Intermediate', '20171012'),
('DDD', 'Inactive', 'Low', '20180202'),
('DDD', 'Inactive', 'Intermediate', '20170921'),
('DDD', 'Inactive', 'High', '20171121'),
('EEE', 'Inactive', 'Intermediate', '20170802'),
('EEE', 'Inactive', 'High', '20180301'),
('EEE', 'Active', 'Low', null);
14 rows affected
with x as
(
select Name, max(EndDate) MaxEndDate
from tbl t1
where not exists (select 1
from tbl t2
where t2.Name = t1.Name
and t2.Status = 'Active')
group by Name
)
select t1.Name, t1.Status, t1.SubStatus, t1.EndDate
from tbl t1
join x
on t1.Name = x.Name
and t1.EndDate = x.MaxEndDate
| name | status | substatus | enddate |
|---|---|---|---|
| BBB | Inactive | High | 2017-12-31 |
| CCC | Inactive | Low | 2017-09-21 |
| DDD | Inactive | Low | 2018-02-02 |