Difficulty with nested query order & grouping

Question

I'm trying to identify the count of objects a user updated during a given day, but only where the user was the final update.

Each object can have multiple updates in a day from any user, but the final update is the one that should be counted.

Each object only has 1 status though--always as a result of the most recent update.

Object_Update_Table

|UpdateID| UserID |timestamp| ObjectID |
| 1      |   3    | today   |    1     |
| 2      |   3    | today   |    2     |
| 3      |   1    | today   |    3     |
| 4      |   1    | today   |    3     |
| 5      |   1    | today   |    1     |

Object_Table

|ObjectID| Status  |
| 1      | failed  |
| 2      | failed  |
| 3      | progress|

Final desired output of query

|User |count "failed"|
| 1   |  1           |
| 2   |  0           |
| 3   |  1           |

Answer 1

This sort of thing can be done fairly elegantly using Window Functions. While I was researching this answer, I found that MySQL doesn't support them. The code that follows is will likely have some performance problems since it uses nested GROUP BY select statements. Proceed with caution.

SELECT UserID
     -- potentially add a date column here
     , sum(Counter) Updates
FROM (   
     SELECT UserID
          , ObjectID
          --- potentially add a date column here
          , 1 Counter
     FROM Object_Update_Table OUT inner join 
          (
          SELECT ObjectID
               -- potentially add a date column here
               , max(timestamp)
          FROM Object_Update_Table OUT
          WHERE timestamp >= today 00:00:00
                AND timestamp < tomorrow
          -- if you add a date column, you'll need to add a GROUP BY clause with the date column here
          ) last_update_day ON OUT.ObjectID = last_update_day.ObjectID
                           AND OUT.timestamp = last_update_day.timestamp
     ) Join_Back_User
GROUP BY Join_Back_User.UserID
     --, potentially add a date column here

The case above only works for one day due to the WHERE clause in line 12 and 13. If you need to do this for more than one day at a time, you should add a function which returns the date to the inner-most sub-query and then carry that date in the Join_Back_User subquery to the outermost query...