I have heavy difficulties in getting rows ordered in an special way. Two tables are part in this:
Table 1 - WORDS:
id | char | word | categoryid (<- id of a row in CATEGORY)
Table 2 - CATEGORY:
id | name
Now I like to get a list of 80 WORDS (LIMIT 0,80) randomly ordered by the categoryid. BUT before there is any new chosen row with an already chosen categoryid all other categoryids should have chosen already. It is not easy to explain, see the following example:
Possible result (CATEGORY has 5 rows with 1 < id <= 5):
id | char | word | categoryid | name 3 f fire 3 nouns 6 n nice 5 adjectives 2 t three 4 numbers 4 e europe 1 places 5 s spanish 2 languages 1 m man 3 nouns 7 b bad 5 adjectives 9 t two 4 numbers 8 a alaska 1 places 10 t turkish 2 languages ...
As you can see the order of the categoryid coloumn should loop. The used words should be randomly chosen and therefore the list should always display different words ordered always differently but in this kind of way.
This task is way to hard for me, I cannot do it in MySQL only, but i really would like to. So if there is anyone genius, please give me advises ;-)