I have a table of the following structure (simplified):
Table orders
id
created
name
executed
category_id
I need a list of n orders from distinct categories (if n
is 5, I should have at most 5 different categories, and never two same categories). I successfully done this one using the following request :
SELECT name
FROM orders
WHERE executed IS NULL
GROUP BY category_id LIMIT 5;
But I'd need to exclude the categories that has already been processed less than an hour ago.
For that, I was able to do the following query :
SELECT name, category_id
FROM orders
WHERE executed IS NULL
AND category_id NOT IN (SELECT category_id
FROM orders
WHERE executed > (UTC_TIMESTAMP() - INTERVAL 60 MINUTE)
GROUP BY category_id)
GROUP BY category_id
LIMIT 5;
But I was wondering if there was a faster way to get the results.
I've made an SQLFiddle to show my example, but I find it quite slow and I was not able to get a successful result from them.
Here's an alternative version with a LEFT JOIN I tried but it doesn't work:
SELECT u1.some, u1.columns
FROM orders AS u1
LEFT JOIN orders AS u2 ON u1.category_id = u2.category_id
AND u2.executed > (UTC_TIMESTAMP() - INTERVAL 5 SECOND)
WHERE u1.created < (UTC_TIMESTAMP() - INTERVAL 60 SECOND)
AND (u1.executed IS NULL
OR u1.executed < (UTC_DATE() - INTERVAL 1 MONTH)
)
AND u2.category_id IS NOT NULL
AND u1.category_id NOT IN u2.category_id
LIMIT 10;