Query max date in one table and group id in another

Question

I need to query the latest purchase for each customer.

Purchases:

purchase_id | date
------------+-----------
 1          | 2020-03-10
 2          | 2020-03-15
 3          | 2020-03-14
 4          | 2020-03-12

Purchase Customer Join Table:

purchase_id | customer_id
------------+------------
 1          | 3
 2          | 7
 3          | 5
 4          | 5

With this query i am able to get the combination of date and customer id. But i need the purchase id too.

SELECT
MAX(p.date), c.customer_id
FROM purchases AS p
JOIN purchase_customer AS c
ON p.purchase_id = c.purchase_id
GROUP BY c.customer_id

I know most databases have specific functions for this kind of problem, but i'd like to have an RDBMS indipendent solution.

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-03-26T21:08:17

A typical "standard" way uses row_number():

SELECT pc.*
FROM (SELECT p.*, c.customer_id,
             ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY p.date DESC) as seqnum
      FROM purchases p JOIN
           purchase_customer c
           ON p.purchase_id = c.purchase_id
     ) pc
WHERE seqnum = 1;

Query max date in one table and group id in another

1 Answers