How to select product that have the maximum price of each category?

Question

The below is my table that has the item such as:

ProductId       ProductName Category        Price
      1            Tiger      Beer          $12.00
      2             ABC       Beer          $13.99
      3            Anchor     Beer          $9.00
      4            Apolo      Wine          $10.88
      5           Randonal    Wine          $18.90
      6            Wisky      Wine          $30.19
      7             Coca     Beverage       $2.00
      8            Sting     Beverage       $5.00
      9             Spy      Beverage       $4.00
     10           Angkor      Beer          $12.88

And I suppose that I have only three category in this table (I can have a lot of category in this table). And I want to show the maximum product's price of each category in this table.

Answer 1

Try this one if you want to get the whole row,

(supports most RDBMS)

SELECT  a.*
FROM    tbProduct a
        INNER JOIN
        (
            SELECT Category, MAX(Price) maxPrice
            FROM tbProduct
            GROUP BY Category
        ) b ON a.category = b.category AND
                a.price = b.maxPrice

If you are using MSSQL 2008+

WITH allProducts AS
(
SELECT  ProductId,ProductName,Category,Price,
        ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY Price DESC) ROW_NUM
FROM tbProduct
)
SELECT ProductId,ProductName,Category,Price
FROM allProducts
WHERE ROW_NUM = 1

or

SELECT ProductId,ProductName,Category,Price
FROM    
(
SELECT  ProductId,ProductName,Category,Price,
        ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY Price DESC) ROW_NUM
FROM tbProduct
) allProducts
WHERE ROW_NUM = 1

SQLFiddle Demo

Answer 2

SELECT   Category,max(Price) as Price
FROM     tbProduct
GROUP BY Category

If you want to retrieve other fields also along with the category name then :

select * 
from  tbProduct T
join   (
         select Category,max(Price) as Price
         from tbProduct
         group by Category)a
on     T.Category=a.Category
and    T.Price=a.Price

Answer 3

select *
from
(Select *, 
        row_number() over (partition by category order by price desc) rn 
        from products) v
where rn=1