CODEWITHSUNDEEP
mysqldatabaserandom
Hugo
Hugo

Reputation: 441

Updating MySQL Table With Random Sorting

I have one table like so

ItemsTable

item_id, cat_id, sort
----------------------
1        1       1
20       1       2
15       1       3
12       1       4
11       1       5
....
1521     1       1991

In my program, every time a page is shown, it will pull the next item based on the sort number. So, say I just showed sort = 2, the next time the page will show item sort = 3 (maybe sort is a bad name).

What I want to do is that once I pull the last item (sort = 1991), I want to re-update the items in that table and re-assign new sort order.

Like this:

ItemsTable

item_id, cat_id, sort
----------------------
35       1       1
7        1       2
2        1       3
1521     1       4
700      1       5
....
5        1       1991

Right now my mind is thinking I have to write a script that SELECTS records where cat_id = 1 sorts the results randomly, then i'd have to create a loop in PHP that would update the database 1001 times. This doesn't seem efficient in my mind.

Can you guys provide a better alternative? What is the most efficient way to do this with MySQL?

Upvotes: 1

Views: 765

Answers (2)

Hugo
Hugo

Reputation: 441

SET @row := 0;
UPDATE ItemsTable SET sort = (@row := @row + 1) WHERE cat_id = 1 ORDER BY RAND();

Ok after some hours of research and trying things out this solved my issue. It now generates a sequence of numbers that are random.

I know that RAND() is not very efficient, but that is another problem for another day :)

Hope this helps someone.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Does this do what you want?

order by cat_id, rand()

Or, are you trying to get the result set twice:

select item_id, cat_id, sort
from ((select t.*, 0 as whichgroup
       from t
      )
      union all
      (select t.*, 1 as whichgroup
       from t
      )
     ) t
order by cat_id, whichgroup,
         (case when whichgroup = 0 then sort
               else rand()
          end)

This outputs each record twice, first in the "sort" order and then randomly.

Based on your comment, this may be what you want:

order by cat_id,
         (case when sort <= 1991 then sort else rand() end)

Upvotes: 0

Related Questions