MYSQL when using UNION keyword the ordering of the first query Ordering is not working

Question

I have query where i want to select featured ads with standard ads in ONE mysql query (one query because website is high traffic so doing two queries in the database will result in performance problems)

*The requirement is to select all the featured ads then select 2 standard ads AND they all must be ordered randomly. *

SO i used the following query, which works fine with the UNION keyword, but the problem is ordering of the RANDOM is only working for the second query.

( SELECT * FROM ads WHERE status='Y' AND priority='High' ORDER BY RAND() )
        UNION (SELECT * FROM ads WHERE status='Y' AND count<=limits 
        AND priority!='High' 
        ORDER BY RAND() LIMIT 0 , 2
)

what i am i doing wrong here ? any ideas ? And As a side question any performance friendly ways to select random rows than using RAND keyword ?

See if this answer helps you: http://stackoverflow.com/a/6397878/1385896 — Filipe Silva, Nov 07 '13 at 01:38

score 1 · Answer 1 · answered Nov 07 '13 at 01:39

You are missing the rand after the union.

SELECT * from
( SELECT * FROM ads WHERE status='Y' AND priority='High' )
        UNION 
( SELECT * FROM ads WHERE status='Y' AND count<=limits AND priority!='High' LIMIT 0 , 2 ) ADS 
ORDER BY RAND()

This creates a Union result set of both the queries as ADS and orders randomly.

score 1 · Accepted Answer · answered Nov 07 '13 at 01:56

Try

SELECT *
  FROM
(
  SELECT * 
    FROM ads 
   WHERE status = 'Y' 
     AND priority = 'High' 
   ORDER BY RAND()
) q
UNION ALL
(
  SELECT * 
    FROM ads 
   WHERE status = 'Y' 
     AND count <= limits 
     AND priority <> 'High' 
   ORDER BY RAND() 
   LIMIT 2
)

Note: Since featured and standard ads don't overlap use UNION ALL instead of UNION

Here is SQLFiddle demo

MYSQL when using UNION keyword the ordering of the first query Ordering is not working

2 Answers2