Is it possible to sort a result set by some column and also by RAND()?
For example:
SELECT `a`, `b`, `c`
FROM `table`
ORDER BY `a` DESC, RAND()
LIMIT 0, 10
Thank you.
views:
31answers:
1
+2
A:
What you are doing is valid - it will order the results in descending order by a but randomize the order of ties.
However to do what you want you need to first use a subquery to get the latest 100 records and then afterwards sort the results of that subquery randomly using an outer query:
SELECT * FROM
(
SELECT * FROM table1
ORDER BY date DESC
LIMIT 100
) T1
ORDER BY RAND()
Mark Byers
2010-10-21 18:00:58
Mark, the output isn't as I expected.
Psyche
2010-10-21 18:04:15
Ties aren't randomized when using `ORDER BY col, RAND()`
OMG Ponies
2010-10-21 18:10:56
Mark, this is not what I want. I want to be able to get the latest (so sorted by date DESC) 100 entries sorted by rand().
Psyche
2010-10-21 18:20:10
What does T1 stand for?
Psyche
2010-10-21 18:32:39
@Psyche: Nothing. It's an alias for the derived table (sub query). You can write something else if you prefer - such as `most_recent_rows` or `i_like_unicorns`.
Mark Byers
2010-10-21 18:46:39