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.

In MySQL, the behavior of selecting a column which is not in your GROUP BY list is undefined. MySQL can return any matched row's value for that column. — Dan Grossman
– Dan Grossman, Commented Feb 4, 2011 at 5:31
A more specific question with test data and expected output would greatly help. — Charles Ray
– Charles Ray, Commented Feb 4, 2011 at 6:05

Gaurav · Accepted Answer · 2011-02-04 06:37:47Z

9

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

edited Feb 4, 2011 at 6:37

answered Feb 4, 2011 at 6:21

Gaurav

28.8k9 gold badges54 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Stewart Over a year ago

With the caveat that if multiple items have the same shortest length, it's undefined which of those items of the same length will be returned.

stenix Over a year ago

I don't think the sum will give you the expected value here. The sum will only be of all the items with exactly the same length as the minimum length. Girish Rao's latest answer below will give you the correct sum as well.

Girish Rao · Accepted Answer · 2011-02-04 05:50:28Z

1

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

should do what you want

edited Feb 4, 2011 at 5:50

answered Feb 4, 2011 at 5:40

Girish Rao

2,6891 gold badge21 silver badges24 bronze badges

3 Comments

Goofy Over a year ago

No, I already thought of that. That query returns the length of the minimum length Item string instead of the Item string itself.

Girish Rao Over a year ago

Ah sorry. Give this a shot

select e.type, max_b.s, e.item from inventory e inner join (select e2.type, sum(e2.amount) as s, e2.item, min(length(e2.item)) as bb from inventory e2 group by e2.type) max_b on (length(e.item)=max_b.bb) group by e.type;

Girish Rao Over a year ago

My answer is in the comment above. Is that the right place for it or should I create a new Answer and put it there?

stenix · Accepted Answer · 2019-08-28 07:55:38Z

1

Not sure where this should go, so I'm creating a new answer:

select e.type, min_item.s, e.item from inventory e 
  inner join 
    (select e2.type, sum(e2.amount) as s, e2.item, min(length(e2.item)) as bb 
     from inventory e2 group by e2.type) min_item 
  on (length(e.item)=min_item.bb) 
group by e.type;

I tested this and it worked.

edited Aug 28, 2019 at 7:55

stenix

3,1362 gold badges21 silver badges32 bronze badges

answered Feb 4, 2011 at 16:39

Girish Rao

2,6891 gold badge21 silver badges24 bronze badges

1 Comment

stenix Over a year ago

You should have edited the first answer instead. Try to remove your first answer since that one is not correct. Otherwise you will get down-votes for that. This is the correct answer to the question.

Collectives™ on Stack Overflow

Return string of minimum length in MySQL database

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related