I would have oversimplified my problem here: How to use MAX in MySQL?
Given a user's log table:
TABLE: user_log - user_log_id (PK) - user_id (FK) - status - timestamp
Things I need: the last user log entry of customers that's a particular
status and it is a minimum of fifteen minutes old. When the user's latest log entry is really a certain status and it is fifteen minutes old, I have to take certain actions during my application.
How do you query this?
datetime type, and
SELECT a.* FROM user_log a INNER JOIN (SELECT user_id, MAX(`timestamp`) as ts FROM user_log b WHERE timestamp <= NOW()-interval 15 MINUTE GROUP BY user_id)b ON (b.user_id = a.user_id AND a.`timestamp` = b.ts)
user_log_id is auto increment, you might choose
MAX(user_log_id) rather than `MAX(timestamp).
select * from user_log where timestamp in (select max(timestamp) from user_log) and status = 'certain' and timestamp > DATE_SUB(now(), INTERVAL 15 MINUTE)
You must do "two queries" to obtain the last entry on the per user basis
Part one gets the final entry for every user, and the second reason is to obtain the data for your entry:
SELECT * FROM user_log u INNER JOIN ( SELECT MAX(user_log_id) as user_log_id, user_id FROM user_log WHERE TIMEDIFF(NOW(), timestamp) <= '00:15:00' GROUP BY user_id ) as a ON u.user_log_id = a.user_log_id
This will highlight only customers which their last timestamp reaches least fifteen minutes old which status is
@a_certain_status. If you wish to find customers their 15-minutes ago log had that status (disregarding any logs within the last 15 miutes, regardless of the status were in individuals logs), use @a1ex07's answer.
SELECT ul.* FROM user_log AS ul JOIN ( SELECT user_id , MAX(`timestamp`) as maxts FROM user_log GROUP BY user_id HAVING MAX(`timestamp`) <= NOW() - INTERVAL 15 MINUTE ) AS ulg ON ulg.user_id = ul.user_id AND ulg.maxts = ul.`timestamp` WHERE ul.status = @a_certain_status