CODEWITHSUNDEEP
sql
Sluna
Sluna

Reputation: 189

Finding Max Price and displaying multiple columns SQL

I have a table that looks like this:

customer_id    item   price   cost
1             Shoe    120     36
1             Bag     180     50
1             Shirt   30      9
2             Shoe    150     40
3             Shirt   30      9
4             Shoe    120     36
5             Shorts  65      14

I am trying to find the most expensive item each customer bought along with the cost of item and the item name.

I'm able to do the first part:

SELECT customer_id, max(price) 
FROM sales
GROUP BY customer_id;

Which gives me:

customer_id      price  
    1             180   
    2             150 
    3             30
    4             120
    5             65

How do I get this output to also show me the item and it's cost in the output? So output should look like this...

customer_id      price   item    cost
    1             180    Bag     50
    2             150    Shoe    40
    3             30     Shirt   9
    4             120    Shoe    36
    5             65     Shorts  14

I'm assuming its a Select statement within a Select? I would appreciate the help as I'm fairly new to SQL.

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

One method that usually has good performance is a correlated subquery:

select s.*
from sales s
where s.price = (select max(s2.price)
                 from sales s2
                 where s2.customer_id = s.customer_id
                );

Upvotes: 1

Related Questions