0

Imagine that I have the following table:

ItemCode WhsCode MinStock MaxStock AvgPrice
123 01 1 4 600
123 02 0 1 200
512 01 0 2 100
489 01 0 6 50
489 02 2 7 72
489 03 5 10 80
489 04 1 4 65
258 01 2 5 45

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:

ItemCode WhsCode MinStock MaxStock AvgPrice
123 01 1 4 600
512 01 0 2 100
489 02 2 7 72
258 01 2 5 45

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 MAX(AvgPrice).

Dialect used is SQL Server 2019. I'm very new to SQL and couldn't find the answer here in an existing post.

Arthur Krelling
  • 3
  • 1
  • 1
  • 3

1 Answers1

0

You can use ROW_NUMBER() to filter to just one row per group.

The PARTITION BY functions like a GROUP BY, except that it does not merge rows, the result is calculated on every row. We then filter it afterwards.

If you want to include tied results then switch to DENSE_RANK().

SELECT T0."ItemCode", T3."MinStock", T3."MaxStock", T3."AvgPrice"

FROM OITM T0


LEFT JOIN (
    SELECT 
            ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY AvgPrice DESC) AS rn
            AvgPrice,
            ItemCode,
            MinStock,
            MaxStock 
    FROM OITW 
    WHERE WhsCode = '01' OR WhsCode = '02' 
) T3 
ON T0."ItemCode" = T3."ItemCode" AND T3.rn = 1

You may find an APPLY faster or slower, depending on the exact query.

In an APPLY the whole subquery is (logically) run for every row of the outer query. You can reference outer columns, and therefore correlate it to the outer tables, there is no join condition as such.

If you add TOP you can then also add ORDER BY.

SELECT T0."ItemCode", T3."MinStock", T3."MaxStock", T3."AvgPrice"

FROM OITM T0


OUTER APPLY (
    SELECT TOP (1)    -- add WITH TIES if you want tied results
            AvgPrice,
            ItemCode,
            MinStock,
            MaxStock 
    FROM OITW T3
    WHERE (T3.WhsCode = '01' OR T3.WhsCode = '02')
        AND T0."ItemCode" = T3."ItemCode"
    ORDER BY AvgPrice DESC
) T3

Note the movement of the join condition and the ordering to the inside, and correct bracketing of the OR. Also be careful to always specify the table for each column

I suggest you choose better aliases than T0 and T3

Charlieface
  • 17,078
  • 22
  • 44