Is there a way to use Order by RAND() for a query so that it returns a list of results and guarantees that adjacent values will be at least 'x' from each other, on either side? The results are all numbers, no strings.

So if it returns 5 results r1 thru r5
abs(r2 - r1) is > 15
abs(r3- r2) is > 15
abs(r4 - r3) is > 15
abs(r5 - r4) is > 15

Eventually I want to get to the point where I can guarantee that no sooner than r6 will a value be < 15 from r1 (remembering that the results are deliberately not sorted iin order, but rather, are random - except for that spacing minimum of 15). Then r7 would be the soonest that a value would be < 15 from r2, etc.

5 Years
Discussion Span
Last Post by chophouse

This is not a simple task. I think this is only possible with a stored procedure, because the next in your result set depends on the previous one. There are quite some issues with this, what if the last row chosen does not fulfill your requirement? Will you start again until all rows meet it?


I would want all rows to meet it. I'm thinking I may have to script this in Python and use the original random list as a feedstock, then evalaute each one. Where it passes, send it to finallist, where it doesn't save it off to failed. Then at the end do again with everything in failed. I'll post here if I ever get it right.


I ended up doing it in Python by following the above procedure. Works like a charm.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.