SQL how to retrieve last ordered 2 of the listed products from all customers?

Question

This is my current query:

SELECT 
    c.name, c.email, c.phone, sol.description, so.orderDate 
FROM 
    SalesOrderLine sol
JOIN 
    SalesOrder so ON sol.salesOrderID = so.id
JOIN 
    Customer c ON so.customerID = c.id
WHERE 
    (orderDate >= '2020-05-01' AND orderDate <= '2020-09-09')
    AND (description LIKE '%Seed Mix%' OR description LIKE '%Sesame Seeds (Natural)%')
ORDER BY 
    c.name

Goal

I am aiming to retrieve where product is seed mix or sesame seeds. And between two dates. But only show the most recent date ordered for each customer for both of the products.

Output:

Question

How can I get earliest date they have ordered for both the 1st or 2nd product mentioned in the query?

Desired output:

Answer 1

You can use row_number():

SELECT *
FROM (
    SELECT c.name, c.email, c.phone, sol.description, so.orderDate,
        RANK() OVER(PARTITION BY c.id, sol.product_id ORDER BY so.orderDate DESC) rn
    FROM SalesOrderLine sol
    JOIN SalesOrder so ON sol.salesOrderID = so.id
    JOIN Customer c ON so.customerID = c.id
    WHERE 
        orderDate >= '20200501' 
        AND orderDate <= '20200909'
        AND (description LIKE '%Seed Mix%' OR description LIKE '%Sesame Seeds (Natural)%')
) t
WHERE rn = 1
ORDER BY name

Note: it seems like you have exact matches on the description. If so, it is more efficient to use equality checks rather than pattern matches. So:

AND description IN ('Seed Mix', 'Sesame Seeds (Natural)')