tim tim - 2 months ago 5
PHP Question

Retrieve first value of group in MySQL?

How can I get the first non-empty-value of a group in MySQL?

I have the following table:

id | item_id | name | description | locale
----------------------------------------------------------
1 | 1 | (null) | (null) | en-GB
2 | 1 | Foo | (null) | en-US
3 | 1 | Bar | Lorem ipsum dolor. | en-AU


I'm trying to return a result like the following?:

item_id | name | description
-------------------------------------------------------------------
1 | Foo | Lorem ipsum dolor.


I am told
coalsece
was the way to go but it doesn't seem to play with groups. Although the shot didn't fire correctly. It should give a good picture of what I'm trying to do.

select item_id, coalesce(name) as name, coalesce(description) as description from test
where item_id = 1
group by item_id
order by field(locale, 'en-GB', 'en-US', 'en-AU');


Other attempts where made using first(), first_value(), min(), max()... I rather accomplish this in a straight MySQL query than mix it with PHP logic.

Answer

You can try something like this:

select 
    item_id,

    substring_index(
        coalesce(group_concat(name separator ','), ''), 
        ',', 
        1
    ) as first_name,

    substring_index(
        coalesce(group_concat(description separator ','), ''), 
        ',', 
        1
   ) as first_description

from test
group by item_id

See note at the bottom regarding order by.

Result

item_id first_name  first_description
1       Foo         Lorem ipsum dolor.
2       

Explanation

Using group_concat function, we are combining all the names and descriptions into a CSV for each item_id.

The result could be null. So we use coalesce to supply an empty string as the value if group_concat resulted in null.

Then we split by comma and get the first piece of information.

The caveat is that your name or description may contain a comma. You will have to account for that somehow, which the query about does not currently.

Live Example or data to try out, if someone wants to

http://rextester.com/RFI82565

create table test (
  id int,
  item_id int, 
  name varchar(100),
  description varchar(200),
  locale varchar(10)
);

insert into test values 
(1, 1, NULL, NULL, 'en-GB'),
(2, 1, 'Foo', NULL, 'en-US'),
(3, 1, 'Bar', 'Lorem ipsum dolor.', 'en-AU'),
(4, 2, NULL, NULL, 'en-GB');

Note

User strawberry noted wisely that my query was missing an order by clause, which OP may find valuable.

Ordering can be done in the group_concat clause and after group by clause. Below is an example of how ordering can be done at both levels. Ordering at group_concat level orders ensures that the first_name is based on the order you provided. Ordering after group by helps the resulting output to be ordered.

select 
    item_id,

    substring_index(
        coalesce(group_concat(name order by field(locale, 'en-GB', 'en-US', 'en-AU') separator ','), ''), 
        ',', 
        1
    ) as first_name,

    substring_index(
        coalesce(group_concat(description order by field(locale, 'en-GB', 'en-US', 'en-AU') separator ','), ''), 
        ',', 
        1
   ) as first_description

from test
group by item_id
order by field(locale, 'en-GB', 'en-US', 'en-AU');
Comments