Q:

Check and select row 2 if row 1 not exist mysql

I have 1 table with name and language columns.
Let's say I have 2 languages available with id 1 and 2 I want to select all the name with language = 2, if any row is not available in language = 2, then I will select the one with language 1. Only get the row with language = 1 if language = 2 not available
ID language name(as code)
1  1        name_1
2  2        name_1
3  1        name_2

I want to get something like this:
ID language name
1  2        name_1
3  1        name_2

Thanks for your help. Sorry for my English

A:

This is a prioritization query.  Here is one method that is pretty fast (particularly with the right indexes):
select t.*
from t
where language = 2
union all
select t.*
from t
where language = 1 and
      not exists (select 1 from t t2 where t2.language = 1 and t2.name = t.name);

This gets cumbersome with more than 2 languages.  For that, you can use order by and variables:
select t.*
from (select t.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @n := '', @rn := 0) params
      order by t.name,
               (case when language = 2 then 1 else 2 end)
     ) t
where rn = 1;

You just adjust the order by clause to put the languages in whatever priority order you want.

