0

I have a table "products" that has the columns "name" and price". There are multiple prices for the same name. The table looks like

shampoo 7
shampoo 10
shampoo 8
bread   1
bread   1.5
water   0.5
water   0.7
......

I want the row with the max price. I tried

select name, price
from products
group by name
having max(price);

Note: My problem isn't that, but I wanna know the logic behind the solution. Thanks.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Billy Grande
  • 577
  • 3
  • 11
  • 23
  • could you see . http://stackoverflow.com/questions/3491329/group-by-with-maxdate – Mohamad Mahmoud Darwish Jun 06 '14 at 12:44
  • possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Strawberry Jun 06 '14 at 12:46
  • Do you want the maximum price overall or for each product? (First select max(price) from products, second: select name, max(price) from products group by name). For the first case you would get the row with the maximum by select * from products where price=(select max(price) from products) – wumpz Jun 06 '14 at 12:49

1 Answers1

3

Create one group or rows for each distinct name using group by name. You can then select the name (because every row in the group has the same name.) The group has many prices, and you can use the aggregate function max() to retrieve the highest price for the group.

select  name
,       max(price) as MaxPrice
from    Products
group by
        name
Andomar
  • 232,371
  • 49
  • 380
  • 404