CODEWITHSUNDEEP
mysql
mahen3d
mahen3d

Reputation: 7754

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

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 ?

Upvotes: 0

Views: 117

Answers (2)

peterm
peterm

Reputation: 92815

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

Upvotes: 1

Jayaram
Jayaram

Reputation: 839

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.

Upvotes: 1

Related Questions