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 tablename (
id INTEGER,
createdDate TIMESTAMP,
status VARCHAR(7)
);
INSERT INTO tablename
(id, createdDate, status)
VALUES
('1', '2021-03-10', 'Active'),
('1', '2021-03-05', 'Pending'),
('1', '2021-03-07', 'Failed'),
('2', '2020-09-20', 'Pending'),
('2', '2020-09-01', 'Active'),
('2', '2020-07-01', 'Failed');
6 rows affected
SELECT id, MAX(createdDate) createdDate, 'Active' status
FROM tablename
WHERE status IN ('Active', 'Failed', 'Pending')
GROUP BY id
HAVING SUM((status = 'Active')::int) > 0 -- 'Active' exists
AND COUNT(DISTINCT status) >= 2 -- at least 2 statuses exist
AND MAX(createdDate) = MAX(CASE WHEN status = 'Active' THEN createdDate END) -- the last date is the date with 'Active' status
| id | createddate | status |
|---|---|---|
| 1 | 2021-03-10 00:00:00 | Active |