Imagine that I have the following table:
I want to select the row with
MAX(AvgPrice) for each item, only from warehouses 01 and 02 (WhsCode).
So the result that I want should look like:
What I tried, in my original code, was:
SELECT T0."ItemCode", T3."MinStock", T3."MaxStock", T3."AvgPrice" FROM OITM T0 LEFT JOIN (SELECT MAX(AvgPrice) AS AvgPrice, ItemCode, MinStock, MaxStock FROM OITW WHERE WhsCode = '01' OR WhsCode = '02' GROUP BY ItemCode,MinStock,MaxStock) T3 ON T0."ItemCode" = T3."ItemCode"
But, since I'm also grouping by MinStock and MaxStock, of course I'll have repeated values for each item, because MinStock and MaxStock are almost never the same for each WhsCode.
So what I want to select is ItemCode, MinStock and MaxStock ONLY for the row with the
Dialect used is SQL Server 2019. I'm very new to SQL and couldn't find the answer here in an existing post.