Remove the middle row of the SQL table

In My SQL Table store the Status record.

There are 4 main status Open, New, Completed, and closed. There can be many New Status. But Open, Completed, and Close should only one status. But we can close status Reopen(ID 102)

For the particular sequence SEQ column is the unique ID. we can consider ID and SEQ as a unique key for the whole table.

If the status is closed then END_TIME should Null. But with the system issue, I’m getting Closed status in the middle. I want to remove that status.

Can you please help me to remove this?

Answer

Hmmm . . You can compare the sequence to the maximum sequence for the id:

select t.*,
       (case when status = 'Closed' and seq <> max(seq) over (partition by id)
             then null else status
        end)             
from t;

This can easily be incorporated into an update query, if you want to actually change the value.