SeanConsidine SeanConsidine - 4 months ago 15
MySQL Question

Return latest entry result providing there is a review or close entry

I have a table that holds the answers to a question which is asked at entry to the system, at review periods and then at closure. The client can be opened and closed multiple times during their life on the system.

I am trying to get the latest 'entry' result from the table which also has either an associated 'review' or 'close' result.

This is my table (I have just included 1 user but the actual table has thousands of users):

row | user_id | answer | type | date_entered |
----+---------+--------+--------+--------------+
1 | 12 | 3 | entry | 2016-03-13 |
2 | 12 | 1 | review | 2016-03-14 |
3 | 12 | 7 | review | 2016-03-16 |
4 | 12 | 7 | close | 2016-03-17 |
5 | 12 | 8 | entry | 2016-03-20 |
6 | 12 | 2 | review | 2016-03-21 |
7 | 12 | 3 | close | 2016-03-22 |
8 | 12 | 1 | entry | 2016-03-28 |


So for this table the query would just return row 5 because the 'entry' on row 8 doesn't have any 'review' or 'closure' records after it.

Hopefully that makes sense.

Answer
SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT x.user_id
            , MAX(x.date_entered) date_entered 
         FROM my_table x 
         JOIN my_table y 
           ON y.user_id = x.user_id 
          AND y.date_entered > x.date_entered 
          AND y.type IN ('review','close') 
        WHERE x.type = 'entry' 
        GROUP 
           BY x.user_id 
     ) b 
    ON b.user_id = a.user_id 
   AND b.date_entered = a.date_entered;