MySQL: select the first item returned by GROUP_CONCAT function

Question

I have a group_concat which returns all the strings from one column. The strings can be variable length. How can I select the first string returned by the group_concat (where 'first' is defined by a group_concat ordering clause)?

Here is a simplified example. From a table of vegetable select the cheapest vegetable from each veg type.

From this table:


veg      type   price
----------------------
carrot   root   1.23
turnip   root   0.45
sprouts  bud    3.56
...

Select this:


selectedVeg   price
-------------------
turnip        0.45
sprouts       3.56
...

My clumsy attempt:

SELECT
    SUBSTRING(
        GROUP_CONCAT(veg ORDER BY price),
        1,
        LOCATE(
            ',',
            CONCAT(GROUP_CONCAT(veg order by price), ',')
        ) - 1
    ) AS selectedVeg
FROM vegTable
GROUP BY type

So for the root veg type, 'GROUP_CONCAT' will return 'turnip, carrot'. Then the locate finds the first comma. And then substring returns all the characters up to this comma. So "selectedVeg" equals 'turnip'.

I've added a CONCAT to ensure there is always one comma for the LOCATE to find.

This doesn't seem very efficient as the GROUP_CONCAT has to be run twice (and it's quite complicated in my actual problem).

Thanks.

is there a reason you are even bothering with the group concat if you are only returning a SINGLE item from the "type" of veggie? I you are not, its MUCH simpler, but can still be done if you ARE using group_concat, just don't want to confuse the FINAL answer... Also, what if two or more veggies have the same "lowest" price... — DRapp, May 20 '11 at 13:53
Thankd @DRapp If I can do it without using a group_concat then this would be great. How do I do this? As 'group' on its own will return a random veg. If two veg are the same price then it doesnt matter which one is returned. — spiderplant0, May 20 '11 at 13:56

score 0 · Accepted Answer · answered May 20 '11 at 14:08

Use a pre-query to get the smallest price per "TYPE", then join to THAT...

select
      v2.veg,
      v2.Price
   from
      ( select v1.type, min( v1.price ) as MinimumPrice
            from Veggies v1
            group by v1.type ) PreQuery
      join Veggies v2
         on PreQuery.Type = v2.type
         and PreQuery.MinimumPrice = v2.price

This query will return ALL vegetables at the lowest price of a given type. If you want a single row, you could change the

v2.Veg to use GROUP_CONCAT( v2.Veg... ) as SelectedVeg

and add a GROUP BY v2.Type to the end of the query.... Your choice.

MySQL: select the first item returned by GROUP_CONCAT function

1 Answers1