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:

enter image description here

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
    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'

1 Answer 1

and not exists (
  select 1 from yourtable t3 
  where t3.id = t1.id 
    and t3.create_date < t1.create_date
    and t3.status = '500'
  • Thanks @mustaccio, simple and works. Learning to get better with subqueries. If you could +1 that would be great, I want to contribute more myself.
    – user173515
    Feb 27, 2019 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.