SQL selecting the maximum value with all the column-CodePudding

The query:

SELECT 
    itemcode, whsecode, MAX(quantity)
FROM
    inventoryTable
WHERE
    itemcode = 'FG 4751'
GROUP BY 
    itemcode;

It 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 or multiple rows with the itemcode 'FG 4751':

The output that I need is

FG 4751|WHSE3|100

CodePudding user response：

SELECT TOP 1 * 
FROM inventoryTable 
WHERE itemcode = 'FG 4751'
ORDER BY quantity DESC

CodePudding user response：

Richard -

Not sure if the plan is to use this for multiple itemcodes at some point. That could be achieved using a window function -

WITH HIGH_RANK AS(
SELECT 
    itemcode, 
    whsecode, 
    quantity,
    RANK(quantity) OVER (PARTITION BY itemcode ORDER BY quantity DESC) AS MAX_RANK
FROM
    inventoryTable)

SELECT
    itemcode,
    whsecode,
    quantity
FROM HIGH_RANK
WHERE MAX_RANK = 1

-RANK(quantity) OVER (PARTITION BY itemcode ORDER BY quantity DESC) AS MAX_RANK

Using rank will leave in all instances where quantity equals the max, if you want only one value, swap RANK for ROW_NUMBER

CodePudding user response：

SELECT * FROM inventoryTable 
WHERE itemcode = 'FG 4751'
ORDER BY MAX(quantity) DESC LIMIT 1