Returning multiple MIN values of a field based upon different criteria

JebusChristmas · #1 (**permalink**) 04-13-10, 18:10

The idea is to find the Min `Inventory` value for each `Tank` for a specified `Site Number` not the single lowest inventory value for a specific `Site Number`

Code:

SELECT DISINCT `Tank`
FROM `detail`
WHERE `Site Number` = 9201906000;

Returns:

|Tank
|-----
|1
|2
|3
|-----

Code:

SELECT `Tank`, `Grade`, Min(`Inventory`) as 'Min Inventory'
FROM `detail`
WHERE `Site Number`= 9201906000 and `Tank` = *Variable Criteria*;

Desired Result: is attached as image

The Field named `Tank` contains a simple 1, 2, 3, or 4 value that identifies the tank.

All fields are located in the same table. I can do this w/ VBScript, but I'm sure there has to be a way using only SQL.

it-iss.com · #2 (**permalink**) 04-13-10, 18:28

Hi Chad,

try using GROUP BY. This should resolve your problem.

SELECT `Tank`, `Grade`, Min(`Inventory`) as 'Min Inventory'
FROM `detail`
WHERE `Site Number`= 9201906000 and `Tank` = *Variable Criteria*
GROUP BY `Tank`, `Grade`;

JebusChristmas · #3 (**permalink**) 04-13-10, 18:32

Well Jebus Christmas, my brain was farting. Thank you for wafting away the fumes.

Internet Services

Web Development

Digital Marketing

Consumer Tech