CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Eric
Eric

Reputation: 248

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

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.

Upvotes: 6

Views: 78971

Answers (7)

AKANKSHA JYOTI
AKANKSHA JYOTI

Reputation: 1

SELECT TOP(7)*,
             Row_number()
               OVER (
                 partition BY category
                 ORDER BY price DESC) rn
FROM   products
ORDER  BY price DESC

Upvotes: 0

John Woo
John Woo

Reputation: 263813

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

Upvotes: 16

Niladri Biswas
Niladri Biswas

Reputation: 4171

Here is another way of doing so where MAX is treated as analytical function (Available since SQL Server 2005)

    Declare @t table(ProductId  int identity, ProductName varchar(100), Category varchar(20), Price numeric(10,2))
    Insert Into @t Select 'Tiger','Beer',12.00
    Insert Into @t Select 'ABC','Beer',13.99
    Insert Into @t Select 'Anchor','Beer',9.00
    Insert Into @t Select 'Apolo','Wine',10.88
    Insert Into @t Select 'Randonal','Wine',18.90
    Insert Into @t Select 'Wisky','Wine',30.19
    Insert Into @t Select 'Coca','Beverage',2.00
    Insert Into @t Select 'Sting','Beverage',5.00
    Insert Into @t Select 'Spy','Beverage',4.00
    Insert Into @t Select 'Angkor','Beer',12.88

SELECT ProductId,ProductName,Category,Price
FROM(
        SELECT
            ProductId
            ,ProductName
            ,Category
            ,Price
            ,CASE WHEN Price =  MAX(Price) OVER(PARTITION BY Category ORDER BY (SELECT 1)) THEN 'Take' ELSE 'Leave' END AS PickUp   

        FROM @t

        )X  WHERE PickUp = 'Take' ORDER BY 1

Result

ProductId   ProductName Category    Price
2           ABC         Beer            13.99
6           Wisky       Wine            30.19
8           Sting       Beverage        5.00

Upvotes: 1

podiluska
podiluska

Reputation: 51504

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

Upvotes: 3

Manish Prajapati
Manish Prajapati

Reputation: 585

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

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24086

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

Upvotes: 4

Oded
Oded

Reputation: 499142

This should work:

SELECT Category, MAX(Price)
FROM Products
GROUP BY Category

This groups the table by each category and uses the MAX aggregate function to get the highest price per each.

Upvotes: 2

Related Questions