SQL selecting the maximum value of multiple items with all the columns

Question

The data:

My query:

SELECT 
    itemcode, whsecode, MAX(quantity)
FROM
    inventoryTable
GROUP BY 
    itemcode;

This returns this error:

Column 'inventoryTable.whsecode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I put the whsecode in the GROUP BY clause, it just returns all the data in the table.

The output that I want is to return the whsecode with the highest quantity of the item in it. The output that it supposed to have is:

whsecode|itemcode|quantity
 WHSE2  |  SS585 |  50
 WHSE2  |  SS586 |  50
 WHSE1  |  SS757 |  30

Eventually I will put that query inside this another query:

SELECT 
    A.mrno, A.remarks, 
    B.itemcode, B.description, B.uom, B.quantity, 
    C.whsecode, C.whseqty, D.rate 
FROM 
    Mrhdr A
INNER JOIN 
    Mrdtls B ON A.mrno = B.mrno
INNER JOIN 
(
    SELECT itemcode, whsecode, MAX(quantity) AS whseqty
    FROM inventoryTable
    GROUP BY itemcode, whsecode
) C ON B.itemcode = C.itemcode
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    A.mrno = @MRNo AND B.quantity < C.whseqty;

with the whsecode inside the GROUP BY clause the output is:

But as I said earlier, the problem is it returns multiple rows of the same itemcode. The output that it supposed to have is:

     mrno      | remarks| itemcode|           description          | uom  |quantity|whsecode|whseqty|  rate
MR211100003008 | SAMPLE | FG 4751 | LONG DRILL 3.4 X 200 L550      | PCS. |  50.00 | WHSE3  | 100   | 0.0000
MR211100003008 | SAMPLE | FG 5092 | T-SPIRAL TAP M3.0 X 0.5 L6904  | PCS  |  20.00 | WHSE1  | 80    | 0.0000

I am not sure if the B.quantity < C.whseqty should be there but it eliminates the other values that are not the maximum value.

Does this answer your question? [Select max value of each group](https://stackoverflow.com/questions/4510185/select-max-value-of-each-group) — Stu, Dec 10 '21 at 12:15

score 2 · Answer 1 · answered Dec 10 '21 at 12:13

There are many ways to solve this. For example, by using the ROW_NUMBER function:

SELECT
    itemcode,
    whsecode, 
    quantity As whseqty
FROM
(
    SELECT 
        itemcode, 
        whsecode, 
        quantity,
        ROW_NUMBER() OVER (PARTITION BY itemcode ORDER BY quantity DESC) As RN
    FROM
        inventoryTable
)
WHERE
    RN = 1
;

AsIndeed · Answer 2 · 2021-12-10T14:31:06.870

0

Edit:

select whsecode,A.itemcode,qty from inventoryTable  
join (SELECT itemcode, MAX(quantity) as qty FROM inventoryTable GROUP BY itemcode) as A on A.itemcode = inventoryTable.itemcode and A.qty = inventoryTable.quantity

edited Dec 10 '21 at 14:31

answered Dec 10 '21 at 12:31

AsIndeed

138
1
13

1

I don't think that would work correctly. Consider what would happen if your data had `A: 1`, `A: 2`, `B: 1`; all three rows would be returned, even though `A: 1` should be excluded. – Richard Deeming Dec 10 '21 at 13:45

SQL selecting the maximum value of multiple items with all the columns

2 Answers2