MySQL using MIN with other attributes to match-CodePudding

This is likely a very simple solution, but I just have no clue. I know I probably have to use GROUP BY, but I don't know what comes after that. Anyway, the code below should output an item that has the lowest available quantity, and show the itemID and name of item that has the lowest quantity.

SELECT ItemID, Inventory_Name, MIN(AvailableQuantity) FROM Inventory;

CodePudding user response：

You have two options:

 SELECT ItemID, 
        Inventory_Name, 
        AvailableQuantity 
 FROM Inventory
 ORDER BY AvailableQuantity  ASC
 LIMIT 1;

But this doesn't handle ties, so you could use a subquery:

SELECT ItemID, 
        Inventory_Name, 
        AvailableQuantity 
 FROM Inventory
 WHERE AvailableQuantity in (SELECT MIN(AvailableQuantity) FROM Inventory i2);