How can I Select Only the Max Value from a Table with Inner Join?

1

Hello

I would like to know how I can Select only the Maximum Value from a Table

For example:

http://sqlfiddle.com/#!9/71a230/1

As you can see:

In my Orders Table, customers can make many orders with the same items.

For example “John Doe” has done 7 Orders and he has ordered Books 2 times,

I would like to see only 1 order per customer and that order would be with the maximum value.

The final desired table would look like this:

Order ID Customer Name Item Name Item Price
7 John Doe Books 40
8 Jane Doe PC 500
10 Mike Cherries 10
11 Julie Tablet 1000
2

Instead of selecting i.price, you should use the aggregate function MAX on i.price and then use the GROUP BY syntax to group on the c.id (CustomerID). I suggest you research these two things to see how they typically work together and then see if you can implement it yourself. If you get stuck, reply back and link to your latest MySQL code attempt.

3