Find date stored in MySQL row, find all entries after that date in one query

Question

I have a table that stores actions for rate-limiting purposes. What I want to do is fetch the newest row that has a 'key_action' (the action that starts the time for rate-limiting) and then find all entries after that date.

The only way I can currently think to do it is with two queries:

1. SELECT created_at FROM actions WHERE key_action=1 ORDER BY created_at DESC LIMIT 1

2. SELECT * FROM actions WHERE created_at >= (created_at from query 1)

Is there a was to combine these two queries into one?

Answer 1

You can make query 1 a subquery of query 2.

SELECT * 
    FROM actions 
    WHERE created_at >= (SELECT MAX(created_at) 
                             FROM actions 
                             WHERE key_action=1)

Answer 2

I'd have thought @Joe Stefanelli's answer was right, but Limits are not allowed in subqueries in WHERE statement. From this workaround, I put together this query (not tested)

SELECT * FROM actions 
JOIN (SELECT created_at FROM actions WHERE key_action=1 ORDER BY created_at DESC LIMIT 1) createdActions
WHERE actions.created_at >= createdActions.created_at