Get max value with a condition

Question

how can get the max status per id? Note that, if the status is successful, then later status must be ignored.

Here is the sample data:

+-------+------------+---------------------+
|  id   |   status   |     created_at      |
+-------+------------+---------------------+
| 76efg | expired    | 2021-01-07 05:19:03 |
| 76efg | pending    | 2021-01-14 06:55:13 |
| fsf56 | successful | 2021-01-25 11:18:03 |
| ghl56 | successful | 2021-01-08 05:19:03 |
| ghl56 | expired    | 2021-02-02 17:37:10 |
+-------+------------+---------------------+

Desired output:

+-------+------------+---------------------+
|  id   |   status   |     created_at      |
+-------+------------+---------------------+
| 76efg | pending    | 2021-01-14 06:55:13 |
| fsf56 | successful | 2021-01-25 11:18:03 |
| ghl56 | successful | 2021-01-08 05:19:03 |
+-------+------------+---------------------+

Answer 1

Here is one approach using analytic functions:

WITH cte AS (
    SELECT *, COUNT(*) FILTER (WHERE status = 'successful') OVER (PARTITION BY id) cnt,
              ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) rn
    FROM yourTable
)

SELECT id, status, created_at
FROM cte t1
WHERE
    (rn = 1 AND cnt = 0) OR
    (cnt > 0 AND status = 'successful' AND NOT EXISTS (SELECT 1 FROM cte t2
                                                       WHERE t2.id = t1.id AND
                                                             t2.created_at > t1.created_at AND
                                                             t2.status = 'successful'));

Demo

The filtering logic in the outer query retains a record under two conditions:

1. No record for a given id group has successful status, in which case we take the latest record, or
2. There are successful records for a given id group, in which case we take the most recent successful record.