SQL query to return maximum value for each identifier-CodePudding

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)

CodePudding user response：

You need to group by each user:

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