How to make a sample "one from each group"?

Question

MySQL

There is a table of "pages", each page has an attribute "language" and "slug". The unique key is "slug"+"language".
It is necessary to select all pages, and give priority to pages with a given "language", if it is not there, then with a default "language"
example:
page1 ru
page2 ru
page3 ru
page1 en
page2 en
page3 en
page1 ch page4
ch
default language en
selection result for ru :
page1 ru
page2 ru
page3 ru
sample result for ch:
page1 ch
page2 en
page3 en
page4 ch

Answer 1

SELECT t1.slug, COALESCE(t2.lang, t3.lang, 'language not found') AS lang
FROM 
(
  SELECT DISTINCT slug
  FROM tbl 
) AS t1

LEFT JOIN tbl AS t2
ON t1.slug=t2.slug
AND t2.lang = 'ru'

LEFT JOIN tbl AS t3
ON t1.slug=t3.slug
AND t3.lang = 'en'

Answer 2

Play around with ORDER BY FIELD ('')
select * from pages ORDER BY FIELD (lang,'uk','ru');
I remember there complexity was that the sorted field goes to the end.
Therefore, it makes sense to prepare the string 'uk','ru' in advance, substituting the default language at the end.
https://dba.stackexchange.com/questions/109120/how...