# Selecting all columns from row with specific max value

## All we need is an easy explanation of the problem, so here it is.

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.

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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`

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂