How to select the most recent entry based on a field in MySQL

Question

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!

Answer 1

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

Answer 2

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.