Mysql – Get results ordered by a specific column in a looping way

MySQL

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 😉

Best Answer

As I've been asked for an example for the SQL Server version, something like:

WITH NumberedWithinCategories AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY NEWID()) AS rownum
  FROM dbo.Words
)
SELECT TOP (80) *
FROM NumberedWithinCategories AS w
JOIN dbo.Categories AS c
  ON c.ID = w.CategoryID
ORDER BY w.rownum, NEWID();

Of course you'd filter the outer query to the columns you want once you've checked the logic...

Related Question