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 column 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 ;-)
ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY NEWID())
in a sub-query to have the items in your categories all numbered randomly. Then you get your top 80 ordering by that field, followed by NEWID(), so that you get all the 1s first (in random order), followed by the 2s, etc.ROW_NUMBER()
.