0

I am very surprised that I can't figure this out.

I'm currently outputting a table from MySQL in a somewhat random order. I say somewhat because there is a formula that is partially reliant on RAND(). In any case, we can assume the order is effectively random for my question.

This was all working great, except I want to keep the same order for a "session". I don't want it to keep jumping around while actively using the data. I have been trying to figure out how to have MySQL generate the same sequence a second time.

I know that you can do RAND(N) where N is a seed, but as far as I can tell that will be the exact same number each time. So basically there will be no random factor at all if I use that.

What I would like is a way I can feed a seed into my ORDER BY and always get a reliable output order. For the same seed, I will get the same order, and if I feed in a different seed, it will be a different random order.

The best I could come up with is that I could create an additional table cell with a RAND for each row and use that for sorting. There are a few issues:

  • Additional memory is used in the database.
  • It doesn't work for multiple users, because I'd need a separate column for each user.

I have to think about this, but I'm pretty certain that there is a clever solution here that doesn't involve me adding an additional column to the database. Has anyone else ever encountered the need to do something like this?

azoundria
  • 940
  • 1
  • 8
  • 24
  • 1
    define the seed value in your application code as a globally accessible constant, and you can access that seed value to query as many times as you want within a session – Madhur Bhaiya Sep 24 '18 at 19:48

2 Answers2

0

As you mentioned, you can provide a seed to generate a sequence of random numbers. The algorithm for generating random numbers returns the same sequence of numbers for the same seed number. For example;

SELECT Rand(1) AS rnd, CustomerId, CustomerName FROM Customers ORDER BY rnd

By doing so you, you will always have the same random order for the seed "1". You can provide session Id or some similar number in order to get the same result.

Hope it helps.

ali
  • 1,301
  • 10
  • 12
0

I would strongly suggest that you use one of the columns to generate the number:

select t.*
from t
order by rand(t.id);

This assumes that id is an integer. You can get a different ordering by adjusting the seed, say, rand(t.id + 1).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I would like to draw your attention to https://stackoverflow.com/questions/27187207/mysql-rand-seed-values-almost-repeat, wherein they show that MySQL's RAND(n) functionality has a tendency to repeat values every four seed increments. Really sucks. – SlowCoder74 Mar 04 '21 at 02:44
  • @SlowCoder74 . . . In this case, it would still be a repeatable "random" number which probably suffices for this purpose. However, the numbers would be more random if another function were used, like say `i * i * sin(i)` or something – Gordon Linoff Mar 04 '21 at 02:53