select * from table where column = something or, when unavailable, column = something else

You can provide a custom ordering, then take the first row, like this:

select * from (
    select *
    from LOCALIZATION_TABLE
    where SET_NAME = 'STD_TEXT'
    order by field(LOCALE, 'ge', '_') -- this provides the custom ordering
) x
group by ENTRY_KEY; -- this captures the first row for each ENTRY_KEY

Explanation:

The inner select’s order by field(LOCALE, 'ge', '_') gets you the rows in the order you define – in this case German first if it exists, then English (you could add more languages to the list).

The “trick” here is using mysql’s “non-standard” GROUP BY behaviour when not listing the non-group columns (most servers treat this as a syntax error) – it simply returns the first row found for every group. The outer select uses group by without an aggregate to get the first row for each named group by.

Output of this query using your data:

+----------+--------+-----------+-------------+
| SET_NAME | LOCALE | ENTRY_KEY | ENTRY_VALUE |
+----------+--------+-----------+-------------+
| STD_TEXT | ge     | GOODBYE   | Lebewohl    |
| STD_TEXT | _      | HELLO     | Hello!      |
+----------+--------+-----------+-------------+

Leave a Comment