Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 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? –  daffunn Feb 14 at 9:32
    
It doesn't work in MySQL, because MySQL doesn't support ROW_NUMBER(). –  Rob Farley Feb 15 at 22:24
add comment

1 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...

share|improve this answer
    
Thank you very much, but I only have MySQL on the server. Is there any way for doing this on a MySQL Server? –  daffunn Feb 16 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 at 22:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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