CODEWITHSUNDEEP
mysqlsql
Roman Tisch
Roman Tisch

Reputation: 219

Customers older than 6 months but not newer

i'm requesting customers from the last 6 months in this way:

SELECT customer
FROM table
WHERE date_of_last_order>date_sub(NOW(),INTERVAL 6 MONTH)
GROUP BY customer
ORDER BY customer ASC

Now i also want a list of older orders than 6 months BUT it should NOT include customers of the last 6 months.

Any ideas? Thank you!

Upvotes: 0

Views: 80

Answers (3)

Jinbo
Jinbo

Reputation: 427

Since the table structure is not described, apparently those of date_of_last_order <= 6 months are what you want. 

SELECT customer
FROM table
WHERE date_of_last_order <= date_sub(NOW(), INTERVAL 6 MONTH)
GROUP BY customer
ORDER BY customer ASC

Upvotes: 0

Mike Robinson
Mike Robinson

Reputation: 8945

Hmm... what do you think, Gordon? Wouldn't the HAVING clause only apply "down-stream," to the rows selected so far?

Another possibility that comes to mind is NOT IN ...

...
WHERE date_of_last_order>date_sub(NOW(),INTERVAL 6 MONTH)
  AND customer NOT IN (
    SELECT DISTINCT customer FROM table 
    WHERE date_of_last_order < date_sub(NOW(),INTERVAL 6 MONTH)
  )
...

... although "it sure is beginning to sound expensive" to me. I'd definitely want to apply the EXPLAIN verb to the various alternatives that I came up with, to find the one that MySQL decides is "cheapest."

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You want to use a having clause:

SELECT customer
FROM table
GROUP BY customer
HAVING MAX(date_of_last_order) <= date_sub(NOW(), INTERVAL 6 MONTH)
ORDER BY customer ASC

Upvotes: 5

Related Questions