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 ;-)

  • 1
    It's quite straight-forward in SQL Server's T-SQL. The idea there would be to use 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. – Rob Farley Feb 14 '14 at 4:23
  • Thank you very much Rob! But can you make an example? I don't understand the whole method :-/ Does this work in a MySQL database? – janwo Feb 14 '14 at 9:32
  • It doesn't work in MySQL, because MySQL doesn't support ROW_NUMBER(). – Rob Farley Feb 15 '14 at 22:24

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

WITH NumberedWithinCategories AS
  FROM dbo.Words
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...

  • Thank you very much, but I only have MySQL on the server. Is there any way for doing this on a MySQL Server? – janwo Feb 16 '14 at 15:47
  • As I wrote in a comment on the question, MySQL doesn't support ROW_NUMBER(). This would be very hard to do using a single operation in MySQL. Your best bet would be to populate a temporary table with the rows, updating them as you go to create a rownum, before querying them back. It would be badly inefficient, and you may prefer to do it in your application, by looping through the categories and fetching rows as you need. – Rob Farley Feb 16 '14 at 22:33

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.