[SQL] How to isolate a value from a group based on a condition on another column?

Question

SQL

CREATE TABLE Items
(
  ItemId INT NOT NULL,
  CategoryId INT NOT NULL,
  ItemValue INT NOT NULL
)

The table contains items. Each item has a category ( CategoryId), as well as some value ( ItemValue).
You need to write a query, the result of which will be grouped by category and will contain the following columns:

• category id;
  
• id of the item with the smallest ItemValuecategory;
  
• id of the item with the largest ItemValuein the category.
  

Performance matters.

Answer 1

select CategoryId,max(id_min_value),max(id_max_value)
from 
(select CategoryId,
case when ItemValue=min(ItemValue) over (partition by CategoryId) then ItemId end as id_min_value,
case when ItemValue=max(ItemValue) over (partition by CategoryId) then ItemId end as id_max_value 
from Items) Items
group by CategoryId

Answer 2

SELECT     CatsMMV.CategoryId, CatsMMV.MinV, CatsMMV.MaxV, MinI.ItemId AS MinItemID, MaxI.ItemId AS MaxItemID
FROM         (SELECT     CategoryId, MIN(ItemValue) AS MinV, MAX(ItemValue) AS MaxV
                       FROM          Items
                       GROUP BY CategoryId) AS CatsMMV LEFT OUTER JOIN
                      Items AS MaxI ON CatsMMV.CategoryId = MaxI.CategoryId AND CatsMMV.MaxV = MaxI.ItemValue LEFT OUTER JOIN
                      Items AS MinI ON CatsMMV.CategoryId = MinI.CategoryId AND CatsMMV.MinV = MinI.ItemValue

Answer 3

Well, for example, like this:

select * from Items a
where not exists (
    select 1 from Items b
     where b.CategoryId = a.CategoryId
       and b.ItemId <> a.ItemId
       and ((b.ItemValue > a.ItemValue) or 
            (b.ItemValue = a.ItemValue 
         and b.ItemId > a.ItemId)));

Answer 4

For the sake of completeness, here is another solution similar to tonyzorin's :

WITH CTE AS
(
  SELECT
    *, 
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
  FROM Items
)
SELECT
  CategoryId, 
  MIN(CASE WHEN MaxRN = 1 THEN ItemId END) IdMaxValue,
  MIN(CASE WHEN MinRN = 1 THEN ItemId END) IdMinValue
FROM CTE
GROUP BY CategoryId