CODEWITHSUNDEEP
mysqlsqlwordpress
Rens
Rens

Reputation: 518

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

Upvotes: 0

Views: 555

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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.

Upvotes: 1

Related Questions