0

My table has multiple entries by individual customers, These customers have made multiple purchases over the years from any of several suppliers listed in my database. I need to run a query to select those whose most recent purchase was from supplier 'A'. If a customer purchased from supplier 'A' last month, but their most recent purchase was, say, yesterday from supplier 'B', then I don't want them included in the result. This may be a simple query, but I'm having trouble getting my head around a reliable method of running this query.

Basically, what I'm looking to generate is:

SELECT 
`customer_email`,
`supplier`,
`purchase_date`
 from `customer_table`
WHERE
`supplier` = 'A' and

... "this purchase by "customer_email" is "customer_email's" most recent purchase, even though they have made multiple previous purchases from any of several 'suppliers'.

Any help in pointing me in the right direction as to completing this query would be greatly appreciated!

2
  • it is hard to tell but a MAX(ourchse_date) gives you the last date, then you need only a GROUP BY to select for which group you want the last purchase date
    – nbk
    Apr 16 '20 at 15:13
  • See the tag I added -- re "groupwise-max"
    – Rick James
    Apr 21 '20 at 16:52
0

One way to make this query simpler is to integrate this requirement during the process of designing the table itself. The purchase table can be made an append-only table(deletes won't make any difference but the purchase must not be editable) and the primary key(id) an auto-increment column. In doing so, the id column becomes directly proportional to the purchase_date column.

The required resultset can be obtained in the following way :

select *
from customer_purchases cp
where cp.id = (
  select max(id)
  from customer_purchases cp_inner
  where cp_inner.customer_email= cp.customer_email 
) and cp.supplier = "supplier_one";

Do let me know if anyone finds any shortcomings with this approach.

The db-fiddle link : db-fiddle

0
WITH CTE AS (
SELECT customer_email,
    supplier,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY customer_email ORDER BY purchase_date DESC) AS rn
 from customer_table
 )
SELECT customer_email,
    supplier,
    purchase_date
FROM CTE 
WHERE supplier = 'A' AND rn = 1

For MySQL versions older than 8 you can use this query:

SELECT customer_email,
    supplier,
    (SELECT MAX(purchase_date) FROM customer_table WHERE customer_email = t.customer_email) AS purchase_date
FROM (
    SELECT c.customer_email,
        (SELECT supplier FROM customer_table WHERE customer_email = c.customer_email ORDER BY purchase_date DESC LIMIT 1) AS supplier
    FROM (SELECT DISTINCT customer_email
            FROM customer_table) AS c
    ) AS t
WHERE supplier = 'A'

this query probably may be revised for better performance but for this I need to see your database structure.

3
  • I'll give it a try and see if that does it. Thanks! Apr 17 '20 at 13:10
  • I'm getting a syntax error in MySQL Workbench at line 1. Could be the version of MySQL I'm running doesn't recognize CTE? Apr 17 '20 at 13:53
  • @JohnCallanan what version of MySQL do you use? Apr 17 '20 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.