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

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.

CodePudding user response：

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
;

CodePudding user response：

Simplest way, you can select your max(quantity) from table with where statement, the query returns the excepted values :

select * from inventoryTable
where quantity in (select max(quantity) from inventoryTable group by inventoryTable.itemcode)