CODEWITHSUNDEEP
mysqlrate-limiting
helloandre
helloandre

Reputation: 10721

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

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?

Upvotes: 1

Views: 150

Answers (2)

Derek Downey
Derek Downey

Reputation: 1532

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135918

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)

Upvotes: 2

Related Questions