Select n random rows for each language ID in one table

Question

I have a table which holds many text entries for many languages, the structure is like this:

`entry` varchar(255),
`language` tinyint,

The language column is an integer type and I have the supported languages pre-defined in my code like LANG_EN = 1, LANG_DE = 2 etc, so I know the maximum possible value.

I want to select only one random row for each supported language ID, preferably with a single query. So if I have 3 supported languages, the query should return 3 rows, with unique language values, therefore having one entry per each language.

I tried it a few ways, with using DISTINCT and GROUP BY but couldn't get it working with a single query.

SELECT * FROM lang_entries GROUP BY language ORDER BY RAND() LIMIT 3

This selects 3 entries of each language, but always the first occurrences in the table (meaning that only the order is random, the entry field is always the same and that's what I want to be random)

SELECT DISTINCT lang, entry FROM test ORDER BY RAND() LIMIT 3;

This kind of works, but doesn't select unique items, sometimes the languages are repeated and I need to have distinct languages.

Answer 1

Something like this, may be:

select language, 
(select entry from entries e1 where e1.language = e.language order by rand() limit 1) entry
from (select distinct language from entries) e

dbfiddle link