Suggest SQL query for given use case

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

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      |