1

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.

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

  • And change LIMIT 1 to something larger -- for the Title request of "select N random rows". – Rick James Jan 5 at 4:01
  • @RickJames Quoting the OP, "I want to select only one random row for each supported language ID". The title seems misleading. – mustaccio Jan 5 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.