Suggest SQL query for given use case

Question

Original Table

Id | Time | Status
------------------
1  |  5   |  T
1  |  6   |  F
2  |  3   |  F
1  |  2   |  F
2  |  4   |  T
3  |  7   |  F
2  |  3   |  T
3  |  1   |  F
4  |  7   |  H
4  |  6   |  S
4  |  5   |  F
4  |  4   |  T
5  |  5   |  S
5  |  6   |  F

Expected Table

Id | Time | Status
------------------
1  |  6   |  F
3  |  7   |  F 
4  |  5   |  F 

I want all the distinct ids who have status as F but time should be maximum, if for any id status is T for given maximum time then that id should not be picked. Also only those ids should be picked who have at-least one T. For e.g 4 will not be picked at it doesn't have any 'T' as status.

Please help in writing the SQL query.

Answer 1

You can use EXISTS and NOT EXISTS in the WHERE clause:

select t.*
from tablename t
where t.status = 'F'
and exists (select 1 from tablename where id = t.id and status = 'T')
and not exists (
    select 1 
    from tablename
    where id = t.id and status in ('F', 'T') and time > t.time
)

See the demo.
Results:

| Id  | Time | Status |
| --- | ---- | ------ |
| 1   | 6    | F      |
| 4   | 5    | F      |