SQL: how to select last purchased product per client?

July 23, 2023

Consider the following tables structure:

invoice: id, client_id
items: id, invoice_id, name, date

My goal is to get the following dataset: client_id, name, date but the name and date are the columns that correspond to the last purchased product.

I tried the following query, but it does not produce the correct result

SELECT 
    i.client_id, ii.name, MAX(ii.date) 
FROM
    invoice i
INNER JOIN
    invoice_item ii ON i.id = ii.invoice_id
GROUP BY
    i.client_id;

>Solution :

You didn’t mention what RDBMS you’re using – if it does support CTE’s (Common Table Expressions) and windowing functions (many of the most used RDBMS do support it), you could use something like this:

WITH Purchases AS
(
    SELECT 
        i.client_id, ii.name, ii.date,
        RowNum = ROW_NUMBER() OVER (PARTITION BY i.client_id ORDER BY ii.date DESC)
    FROM
        invoice i
    INNER JOIN
        invoice_item ii ON i.id = ii.invoice_id
)
SELECT
    p.client_id, p.name, p.date
FROM
    Purchases
WHERE
    p.RowNum = 1;

The Purchases is a CTE – sort of an "one-off, inline view"; using the ROW_NUMBER function, you "partition" your data by client_id, and all purchases for each client are sequentially numbered, ordered descendingly by purchase date – so the most recent purchase for each client will always have RowNum = 1 – and that’s exactly what I select from that CTE.