Question 20992 in Mysql Question posted in
The official MySQL documentation can be found here.

How to select latest purchase of customer using two or more fields in group by clause? – Mysql

I have two tables, from which I need to select the latest purchase of each customer from the US. The query must present the user_id and the purchased item columns:

(I left out some of the records from both tables)

customers

user_ID created_at country
A34212213 2021-06-03 14:31:35 USA
BK76584I 2022-01-02 14:31:35 USA

purchases

user_ID date_of_purchase purchased_item
A34212213 2021-12-30 01:54:07 Hamburger
A34212213 2021-12-02 17:02:59 Chips
A34212213 2021-12-01 06:37:59 Potatos
A34212213 2021-12-22 12:02:39 Hamburger
BK76584I 2021-12-02 08:55:30 Ice
BK76584I 2021-12-19 12:22:12 Gummies
BK76584I 2021-12-15 22:07:43 Hotdogs

the result would be:

user_id purchased_item
A34212213 Hamburger
BK76584I Gummies

I tried writing this query:

SELECT c.user_id, purchased_item, MAX(date_of_purchase) FROM customers c JOIN purchases p ON c.user_id = p.user_id WHERE country = 'USA' GROUP BY c.user_id, purchased_item;

But it still gives me multiple records of the same user_ID, because the purchased_item is different in most records. I can’t seem to figure out how to get only one record of each user_ID and their latest purchase, using two or more fields in the gorup by clause.

Thanks in advance for all the help! 🙂

Tags:

2

Answers


  1. 0

    The subquery filters the last item each user bought.

    SELECT c.user_id, p.purchased_item
FROM customers c
JOIN purchases p ON c.user_id = p.user_id
WHERE country = 'USA' AND date_of_purchase = 
  (SELECT MAX(date_of_purchase) FROM purchases p2 WHERE p2.user_id = c.user_id);

    TEST

    http://sqlfiddle.com/#!9/1ffae8/2

    Login or Signup to reply.
  2. 0 
    WITH CTE AS (
SELECT c.user_id, p.purchased_item, p.date_of_purchase,
ROW_NUMBER() OVER (PARTITION BY c.user_id ORDER BY p.date_of_purchase DESC) AS rn
FROM customers c
JOIN purchases p
ON c.user_id = p.user_id
WHERE country = 'USA'
)
SELECT user_id, purchased_item
FROM CTE
WHERE rn = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search