skip to Main Content

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

2

Answers


  1. 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. (
        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