renzz17 renzz17 - 7 months ago 18
SQL Question

ORDER BY RAND() not working within a UNION

I have some problems with ORDER BY RAND() within a UNION. It doesn't seem to order randomly within the seperate queries within a UNION.

I only want to order the sub queries without ordering the whole query at once as the order needs to be Subquery 1 > subquery2 > subquery3.

I've tried some other random functions for MySQL like NEWID() and UUID() but they don't seem to work either.

I'm using WordPress to get the results.

SELECT *
FROM (
(SELECT act.wordpress_id,
act.title,
act.main_image,
loc.id,
"activities" AS type,
loc.town,
loc.village
FROM tha_wc_activity act
INNER JOIN tha_wc_location loc
ON act.location_id = loc.id
WHERE
act.active = 1 AND
act.visible_in_activities_overview = 1 AND
loc.id = 1
ORDER BY RAND()
)
UNION
(SELECT act.wordpress_id,
act.title,
act.main_image,
loc.id,
"activities" AS type,
loc.town,
loc.village
FROM tha_wc_activity act
INNER JOIN tha_wc_location loc
ON act.location_id = loc.id
WHERE
act.active = 1 AND
act.visible_in_activities_overview = 1 AND
loc.village = "villageName"
ORDER BY RAND()
)
UNION
(SELECT act.wordpress_id,
act.title,
act.main_image,
loc.id,
"activities" AS type,
loc.town,
loc.village
FROM tha_wc_activity act
INNER JOIN tha_wc_location loc
ON act.location_id = loc.id
WHERE
act.active = 1 AND
act.visible_in_activities_overview = 1 AND
loc.town = "townName"
ORDER BY RAND()
)
) AS act
WHERE act.wordpress_id != 1
LIMIT 0, 15)


UPDATE:
Thanks to Gordon Linoff, I found out that I had to put limit on the seperate subqueries else it wouldn't be getting the ordered results

Answer

I'm not quite sure what your question is. You are using order by rand() in subqueries, but then selecting all the results. Getting all the rows is all the rows, regardless of their ordering.

Then, you are using union which removes duplicates and have other logic as well.

Presumably, you want something like this:

select *
from (<subquery 1>
      union
      <subquery 2>
      . . .
     ) t
where . . .
order by rand()
limit 15;

There is no need to order the subqueries, unless you use limit.

Note that you might want to use union all rather than union -- unless you explicitly want to incur the overhead of removing duplicates.

Comments