MySQL UNION 2 queries containing ORDER BYs

Question

I am trying to UNION two queries which both contain ORDER BY's. As I have discovered, you can not order by queries that are part of a UNION. I just don't know how else to do this query then. Let me explain what I'm trying to do.

I am trying to select the 40 most recent profiles and from that list select a random set of 20. I then want to union that with:
Select 40 random profiles where the profile does not fall in the original 40 most recent profiles queried in the first set
Randomly order that whole set of 60 records.

I am aware of the efficiency ramifications of using the Rand() function

SELECT profileId
  FROM (SELECT profileId
          FROM profile profile2
         WHERE profile2.profilePublishDate <= Now()
      ORDER BY profile2.profilePublishDate DESC
         LIMIT 0,40) AS profile1
ORDER BY RAND()
   LIMIT 0,20
UNION (SELECT profileId
         FROM profile profile4
        WHERE profileId NOT IN (SELECT profileId
                                  FROM profile profile4
                                 WHERE profile4.profilePublishDate <= Now()
                              ORDER BY profile4.profilePublishDate DESC
                                 LIMIT 0,40)    
     ORDER BY RAND()    
        LIMIT 0,40) as profile3
ORDER BY RAND()

UPDATE: This is the solution based on Abhay's help below (thanks Abhay):

SELECT *
FROM
(
    (
        SELECT profileId
        FROM 
        (
            SELECT profileId
            FROM profile profile2
            WHERE profile2.profilePublishDate <= Now()
            ORDER BY profile2.profilePublishDate DESC
            LIMIT 0,40
        ) AS profile1
        ORDER BY RAND()
        LIMIT 0,20
    )
    UNION
    (
        SELECT profileId
        FROM profile profile4
        WHERE profileId NOT IN (
            SELECT * FROM
            (
            SELECT profileId
            FROM profile profile4
            WHERE profile4.profilePublishDate <= Now()
            ORDER BY profile4.profilePublishDate DESC
            LIMIT 0,40
            ) AS temp2
         )
        ORDER BY RAND()    
        LIMIT 0,40
    )
) TEMP
ORDER BY RAND();

score 5 · Accepted Answer · answered Jun 18 '11 at 18:03

This is your solution:

SELECT *
FROM
(
    **(**
        SELECT profileId
        FROM 
        (
            SELECT profileId
            FROM profile profile2
            WHERE profile2.profilePublishDate <= Now()
            ORDER BY profile2.profilePublishDate DESC
            LIMIT 0,40
        ) AS profile1
        ORDER BY RAND()
        LIMIT 0,20
    **)**
    UNION
    (
        SELECT profileId
        FROM profile profile4
        WHERE profileId NOT IN (
            SELECT profileId
            FROM profile profile4
            WHERE profile4.profilePublishDate <= Now()
            ORDER BY profile4.profilePublishDate DESC
            LIMIT 0,40
            )
        ORDER BY RAND()    
        LIMIT 0,40
    )
) TEMP
ORDER BY RAND();

The changes that I've made are:

each of your queries that are part of UNION should be encased in brackets (shown in bold for the first query; the second is already encased)
removed the alias profile3 for your 2nd query
for the final ORDER BY RAND(), you must create the UNION resultset to a derived table; I have given it TEMP as the alias

I haven't tested the above query but I hope it should work. Let me know your findings.

Thanks - You solution solved my problem however a new error arose (This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery') which I have also resolved with another select and posted the full query above in my original post. Thanks for your help in solving this. — Paolo Broccardo, Jun 19 '11 at 09:38
@Cheeky: Yes, this is the way to solve it, with the limitations that MySQL poses :) — ypercubeᵀᴹ, Jun 19 '11 at 09:51

Andy · Answer 2 · 2011-06-18T18:23:00.493

1

You can encase the entire query in another select (to make is a subselect) and ORDER BY RAND() on that results set. This is quite convoluted however (many random ORDER BY statements when you only require one), so it's likely to be less processor intensive to collect the ordered data set (i.e. just the UNION of profile1 and profile3) into an array in your language of choice and randomise that order of that array.

edited Jun 18 '11 at 18:23

answered Jun 18 '11 at 17:10

Andy

17,423
9
52
69

He does need the inner `ORDER BY RAND()` – ypercubeᵀᴹ Jun 18 '11 at 17:13
@ypercube why is the inner `ORDER BY` required when the whole results set is re-ordered? – Andy Jun 18 '11 at 17:55
because it is used in combination with `LIMIT 40` to choose 40 random rows from one part of the table and 20 random rows from another (with `LIMIT 20`). – ypercubeᵀᴹ Jun 18 '11 at 17:59
@ypercube thanks, forgot to re-RTFQ after your 1st comment, answer updated ;) – Andy Jun 18 '11 at 18:24

MySQL UNION 2 queries containing ORDER BYs

2 Answers2

Linked