Geek Answers Handbook

MySQL UNION 2 queries containing ORDER BYs

I am trying to execute UNION two queries that contain ORDER BY. As I discovered, you cannot order requests that are part of UNION. I just don't know how else to make this request. Let me explain what I'm trying to do.

  • I am trying to select the last 40 profiles, and from this list, select a random set of 20. Then I want to combine this:
  • Select 40 random profiles in which the profile does not fall into the original 40 last profiles requested in the first set
  • Randomly order the entire set of 60 entries.

I know the implications of using Rand () efficiently

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 solution is 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();
+5
2

:

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();

, , :

  • , UNION, ( , )
  • profile3
  • ORDER BY RAND(), UNION ; TEMP

, , . .

+5

( ) ORDER BY RAND() . ( ORDER BY , ), (.. UNION profile1 profile3), , ) .

+1

More articles:


All Articles