How to select the most recent entry based on a field in MySQL 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!
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.