I got the following table articles:

I would like to get the highest price of each category.

The result would be:

select Max(price), ID, category from article group by ID,category returns:

Unfortunately I get both rows for category 1. But I only would like to have the highest price in category 1 which is 55. Can someone help me?

see above

CodePudding user response：

Try this...

I've reproduced your sample data and then added a rnk column which ranks by price descending witin each category, used this in the subquery and just returned anything where rank is 1.

DECLARE @articles TABLE (ID int, Category varchar(20), Price float)
INSERT INTO @articles VALUES 
(1, 'category1', 10),
(2, 'category1', 55),
(3, 'category2', 15),
(4, 'category3', 20),
(5, 'category4', 25)

SELECT 
    ID, Category, Price
    FROM (
            SELECT 
              ID, Category, Price
            , RANK() OVER(PARTITION BY Category ORDER BY Price DESC) as rnk
            FROM @articles
        ) a
WHERE a.rnk = 1   

Which gives these results

Bote If you have two articles for the same category with the same price, both will be returned.

CodePudding user response：

--===== This is NOT a part of the solution.
     -- We're just making "Readily Consumable Test Data" here.
     -- This is how you should post sample data to help those
     -- that would help you.  You'll get more thumbs up on your
     -- questions, as well
 SELECT *
   INTO #Articles
   FROM (VALUES
         (1, 'category1', 10)
        ,(2, 'category1', 55)
        ,(3, 'category2', 15)
        ,(4, 'category3', 20)
        ,(5, 'category4', 25)
        )d(ID,category,price)
;
--===== One possible easy solution that will also display "ties".
   WITH cteRankByCategory AS
(
 SELECT *,DR = DENSE_RANK() OVER (PARTITION BY Category ORDER BY Category, Price DESC)
   FROM #Articles
)
 SELECT ID,Category,MaxPrice = Price
   FROM cteRankByCategory
  WHERE DR = 1
  ORDER BY Category
;