Retrieve first value of group in MySQL?

Question

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.

Possible duplicate of [Select column, if blank select from another](http://stackoverflow.com/questions/1747750/select-column-if-blank-select-from-another) — zanderwar, Sep 04 '16 at 02:01
@Zanderwar: Would you know the difference of a row and a column? You just linked up a solution to alternative columns. — tim, Sep 04 '16 at 02:03
Sorry @Tim, does a row not return a set of columns, one of which you want to check is empty and if so fall back to another table & column? Your join: http://www.w3schools.com/sql/sql_join.asp - could always check on php side too — zanderwar, Sep 04 '16 at 02:05
What determines the order of the languges? Foo is no matched with lorem in the table data. Can you pls add details of what you want to achieve? — Shadow, Sep 04 '16 at 02:13
Yeah tbh, why would you fallback to a different language, should just return the lang key and leave it to the developer to ensure all lang keys are available. — zanderwar, Sep 04 '16 at 02:17
@Shadow The order should be determined by a defined order. I provided an attempt. Hope it gives you a better picture. — tim, Sep 04 '16 at 02:31
@Zanderwar I'm a developer not the user maintaining the database. How would you like to translate 11,000 articles. No? Think a blank nothing is better? The user wants a fallback translation and I'm not gonna discuss why. Let's just say it has practical and professional benefits for this client. — tim, Sep 04 '16 at 02:35
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 13 '16 at 13:31

zedfoxus · Accepted Answer · 2016-09-13T13:47:51.640

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');

Good eye, @Strawberry. Thank you. I have added some notes at the bottom of the answer. — zedfoxus, Sep 13 '16 at 13:48
I have accepted your answer as the best answer to my question. Thanks :) — tim, Sep 14 '16 at 12:38

score 2 · Answer 2 · answered Sep 15 '16 at 21:07

Similar to @zedfoxus's solution, but using a Standard SQL solution working in every DBMS:

select 
    item_id,

    coalesce(max(case when locale = 'en-GB' then name end)
            ,max(case when locale = 'en-US' then name end)
            ,max(case when locale = 'en-AU' then name end)) as first_name,

    coalesce(max(case when locale = 'en-GB' then description end)
            ,max(case when locale = 'en-US' then description end)
            ,max(case when locale = 'en-AU' then description end)) as first_description

from test
group by item_id

M T Head · Answer 3 · 2016-09-16T16:33:43.480

0

select TT2.Id, TT2.Item_Id, TT2.MyName, TT2.MyDesc, TT2.Locale from #TempTest TT2 
where convert(Varchar(12),TT2.Id) + '#' + convert(Varchar(12),TT2.Item_Id) 
in 
(
select X = convert(Varchar(12),Min(TT1.Id)) + '#' + convert(Varchar(12),Min(TT1.Item_Id))
from #TempTest TT1
where TT1.MyName is not null
group by TT1.Locale
)

edited Sep 16 '16 at 16:33

answered Sep 14 '16 at 23:36

M T Head

1,085
9
13

Would you come up with the result I demonstrated that way? – tim Sep 15 '16 at 00:30
This returns the results your discussing. You are trying to do something with grouping but with the data set you provided I can't follow what you are trying to group. May need to add some data for a Second Item Id data set in your example so that a group by can be demonstrated with the sample data. – M T Head Sep 15 '16 at 16:43
That would return a result with the name Bar. My example shows Foo. I need the "first value" of "group" in that certain process order. Simply what are the first column values of a grouped result. – tim Sep 15 '16 at 17:37
So let me clarify what you seek. You want to ignore data where name = null, you don't care that description is null. Then after that you want to select the lowest Id number for each grouping of Item_Id records? "First" is kind of meaningless in sql. The way the records return are not always going to be the same. You have to define first based on a value or alpha sort or something like that. Or do you want the first by Locale? Is this what you are trying for? – M T Head Sep 15 '16 at 18:59
Poorly phrased. What logic decides which record you choose between the choices of Id=2 and Id=3? Alpha Sort on Locale or lowest Id? – M T Head Sep 15 '16 at 19:00
If you group all the column values accoarding to the sort order by field(locale, 'en-GB', 'en-US', 'en-AU') I want to grab the first value of every group. Just the FIRST value. Null or empty string is not a value in this case. – tim Sep 16 '16 at 14:15
So because en-AU alphabetically comes before en-US, that is why it is included. Your question kind of did suggest that I just want to be clear because I would not use a group by type solution for this request correctly. That confused me and made me think I was not following the request. Solution coming in a few minutes. – M T Head Sep 16 '16 at 16:16
Ok I edited the answer. The new answer should be what you seek. The trick to this issue is to break the query into two parts. One to identify what records you want to find. The other to return what fields you want to see for the records you want to find. Try to combine them into a single step and you will likely fail due to the way sql works and the min/max stuff. The direction your initial query wend down with a single query will not work within your desired constraints. You have to use a Nested Sql statement for it to work. – M T Head Sep 16 '16 at 16:36

score -1 · Answer 4 · answered Sep 13 '16 at 10:40

-1

Im not sure but I think the following query will retrieve only the field where there are no null values

 SELECT item_id,name,description,locale FROM test WHERE (name != '' AND description != '')

Hope it works

answered Sep 13 '16 at 10:40

ocnet

51
5

1

You can also iterate using loop to retrieve all other rows with not null values – ocnet Sep 13 '16 at 10:45
This question doesn't add up leave alone the solutions provided – ocnet Sep 15 '16 at 06:21

Retrieve first value of group in MySQL?

4 Answers4