MYSQL Select/Group By: How do I remove a group from the result based on a value of one of it's members?

Question

Consider the table of events below. The OrderID field is a foreign key to an Orders table.

OrderID     EventType     TimeAdded*       Processed
   1           ...        3 Hours Ago          0
   2           ...        5 Minutes Ago        0
   1           ...        2 Hours Ago          0
   1           ...        1 Hour Ago           0    
   3           ...        12 Minutes ago       1
   3           ...        3 Hours Ago          0
   2           ...        2 Hours Ago          0

*TimeAdded is actual a mysql date-time field. I used human readable times here to make the question easier to read.

I need to get a result set that has each OrderID, only once, but only if ALL records attached to a given order ID, that have a proceeded status of 0, have been added more than 15 minutes ago.

In this example, order #2 should be OMITTED from the result set because it has an unprocessed event added within the last 15 minutes. The fact that it has an unprocessed event from 2 hours ago is inconsequential.

Order #3 SHOULD be included. Even though one of its entries was added 12 minutes ago, that entry has already been processed, and should not be considered. Only the "3 hours ago" entry should be considered in this set, and it is greater than 15 minutes ago.

Order #1 SHOULD be included, since all three unprocessed events attached to it occurred more than 15 minutes ago.

I'm not sure if this needs to use a sub-query or group by or possibly both?

pseudo code:

SELECT * FROM OrderEvents WHERE Processed=0 GROUP BY OrderID
    OMIT WHOLE GROUP IF GROUP_MIN(TimeAdded) >= (NOW() - INTERVAL 15 MINUTE)

I'm just not certain of the correct syntax?

Answer 1

I think it's something along these lines:

SELECT OrderID
FROM OrderEvents
WHERE Processed=0
GROUP BY OrderID
HAVING MAX(TimeAdded) < (NOW() - 1500);

Bear in mind, that I'm working blind here. I think 1500 is correct, for the function now() used in a numeric context. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

Answer 2

This will do the work (tested in MySQL Workbench):

SELECT * FROM OrderEvents WHERE Processed = 0 GROUP BY OrderID
    HAVING MAX(TimeAdded) < (NOW() - INTERVAL 15 MINUTE);