Mysql Question posted in
The official MySQL documentation can be found here.

Problems with MySQL using rand() with union

I need to return a short list of products. I want featured items first in random order, and then non-featured items making up any shortfall, also in random order.

I have got this statement but while the two separate SELLECT statements work individually when in the UNION they always bring back the same "Random" records

SELECT  p1.*
FROM    (   SELECT  ID, Name, Featured
            FROM    Product
            WHERE   Featured =  1
            ORDER BY RAND()
        )   p1
UNION
SELECT  p2.*
FROM    (   SELECT  ID, Name, Featured
            FROM    Product
            WHERE   Featured =  0
            ORDER BY RAND()
        )   p2
LIMIT   0,6
Tags:

2

Answers


  1. 0

    Try the following Query:

    SELECT  p1.*
FROM    (   SELECT  ID, Name, Featured, CONCAT('A', RAND()) AS rand_value
            FROM    Product
            WHERE   Featured =  1
        )   p1
UNION
SELECT  p2.*
FROM    (   SELECT  ID, Name, Featured, CONCAT('B', RAND()) AS rand_value
            FROM    Product
            WHERE   Featured =  0
        )   p2
ORDER BY rand_value
LIMIT   6;
    Login or Signup to reply.
  2. 0 
    (
    SELECT  ID, Name, Featured
    FROM    Product
    WHERE   Featured = 1
    ORDER BY RAND()
    LIMIT 3 -- Assuming you want a maximum of 3 featured items
)
UNION ALL
(
    SELECT  ID, Name, Featured
    FROM    Product
    WHERE   Featured = 0
    ORDER BY RAND()
    LIMIT 3 -- Assuming you want a maximum of 3 non-featured items
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search