Select only 1 raw each for a product 2

[dnagahawatte]

Dear support;
If i have a prodyct table with different product instances with the same product name and all those instances have a different price; How can i only select product instances with the highest price.
Product / Name / Price
Car - BMW £10000
Car - MB £15000
Car - VW £7000
Computer - IBM £500
Computer - DELL £250
Computer - HP £750

Out come should be
Car - MB £15000
Computer - HP £750

Thanks

 

[SQLDenis]

something like this.....untested

select p.product,p.name,p.price from
product p join (select max(price) as Maxprice,product
from product
group by product ) pp on p.product =pp.product and p.price =pp.price

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AtomicWedgie]

shouldn't it be

and p.price =pp.Maxprice

Regards,

AW

 

[SQLDenis]

Thanks,yes it should be
I didn't do in QA so I couldn't test it
So this is the correct answer

select p.product,p.name,p.price from
product p join (select max(price) as Maxprice,product
from product
group by product ) pp on p.product =pp.product and p.price =pp.Maxprice


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dnagahawatte]

Excellent, Thanks both of you!