SQL Shuffle/Randomise database then order sample by column

Question

What I'm trying to achieve is to return a random sample of x size from a dataset, then order it based on a column. This is what I have tried:

SELECT *
FROM Table
WHERE integerField > 0
ORDER BY RAND(), integerField DESC
LIMIT 100

The idea here is that it will first order the table by random, effectively shuffling it, then order the first 100 rows returned by the integerField. I believe the problem is that it does not do the limit before the order, so I'm either going to get 100 random lines back or the first 100 lines of the database ordered by score (In this example, it's the former)

Is there a way to achieve this in a single query, or will the output have to be manually parsed through external logic/additional queries?

https://mitchwheat.com/2011/08/07/t-sql-generating-random-numbers-random-sampling-and-random-goodness/ — Mitch Wheat, May 30 '22 at 12:32
https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, May 30 '22 at 12:33
Do ORDER BY rand and LIMIT in a subquery. Then order its result. — jarlh, May 30 '22 at 12:36
If you do: `SELECT RAND() FROM Table LIMIT 100` what do you see? — Magnus, May 30 '22 at 12:38
Add an order by to this answer. https://stackoverflow.com/a/55916955/87015. Does not guarantee the exact number of rows. — Salman A, May 30 '22 at 12:49
Needed sorting levels are independent. So you need a subquery - `SELECT * FROM ( SELECT * FROM table ORDER BY RAND() LIMIT 100 ) x ORDER BY integerField DESC`. — Akina, May 30 '22 at 13:20

score 0 · Answer 1 · answered May 30 '22 at 19:30

0

Solution: Utilise a substring to collect the initial randomised sample, then order it:

(SELECT * FROM Table
WHERE integerString >= 0 
ORDER BY RAND() LIMIT 100) 
ORDER BY integerField DESC

Credit to Akina and jarlh for the pointer to use substring

answered May 30 '22 at 19:30

Darth

63
11

SQL Shuffle/Randomise database then order sample by column

1 Answers1