Return string of minimum length in MySQL database

Question

I have a table called "Inventory" with the fields Item (varchar), Amount (int), Type (varchar)

What I would like to extract is rows with the following fields:

Shortest entry in the Item field of all Items of type Type

Sum of all Amounts of all Items of type Type

I have the following:

SELECT Item, sum( Amount ) FROM Inventory GROUP BY Type

which gives what I want except it doesn't return the shortest Item, instead it has one of the other Items (the last one it finds I think). By shortest I mean minimum string length.

Thanks for any help.

Answer 1

You can get it by sub query.

select type, sum(amount), item 
from inventory 
group by type
having length(item) <= (select min(length(item)) from inventory)

User Order By columnName ASC /DESC for sorting and LIMIT 1 for getting one out of that