Skip to main content
wrap
Source Link
Rick James
  • 79.4k
  • 5
  • 51
  • 117

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;

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;

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;
added 458 characters in body
Source Link

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;

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.

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;
added 9 characters in body
Source Link
McNets
  • 23.9k
  • 11
  • 50
  • 89

I have a table of the following structure (simplified):

Table orders

  • id
  • created
  • name
  • executed
  • category_id
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.

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.

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.

Source Link
Loading