SQL query to return maximum value for each identifier

Diamaudix Audio Ltd. 0
Viewed 28

In a table 'purchases' is a list of items bought at a 'cost' by each 'user_id'

One user can make many purchases, so there are many duplicate user_id's in the table.

What could be an SQL query that would return only the most expensive item that each user had purchased?

What is the error in the following attempt?

SELECT user_id, MAX(cost) 
FROM purchases
WHERE user_id 
IN (SELECT user_id FROM purchases)
select database sql
1 Answers

You need to group by each user:

SELECT user_id, MAX(cost) 
FROM purchases
GROUP BY user_Id;
userqKk210
Related