Formatting query to respond to past column values I'm writing a query that follows the process of forms being processed through various SQL functions - they receive status numbers as they go through the process. I want to pull forms that are in a very specific situation: That (at most recent date) have code 599, but have NOT HAD code 550 in the past. For instance if I have the table:



If I performed a query of grabbing the counts that satisfy the table above, the return should be 1. Again, I am trying to figure out how to write a query that pulls forms that most recently have 599, but has not also had 550 in the past. Assume the table name is just "table."

My best attempt can only get the count of forms where the most recent date is 599, but I'm struggling to account for the past 550:

select t1.* FROM table t1
JOIN
(
    select id, MAX(create_date) AS MAXDATE
    FROM table
    GROUP BY id
) t2
ON T1.id = t2.id
AND t1.create_date = t2.MAXDATE
where status = '599'
and not exists (
  select 1 from yourtable t3 
  where t3.id = t1.id 
    and t3.create_date &lt; t1.create_date
    and t3.status = '500'
)