Select random rows but without duplicates of values from one column

Question

This is a simple select from a single table. The purpose is to select four random products, one from each of x number of categories, with a couple of 'where' limitations. I've tried this:

  SELECT pName, 
         pID 
    from products 
   WHERE pDisplay=1 
     AND pFeatured=1 
GROUP BY pCategory 
ORDER BY RAND() 
   LIMIT 0,4

This kind of works, but it always returns the same product from any given category. I want to vary the products shown, while still showing only a single product for any given category.

I also tried:

  SELECT DISTINCT(pCategory) 
         pName, 
         pID 
    from products 
   WHERE pDisplay=1 
     AND pFeatured=1 
ORDER BY RAND() 
   LIMIT 0,4

I'm thinking maybe it needs two selects -the first to get a random 4 categories the second to choose a random row from each of them, but a. am not sure how to do this, and b. would prefer to use a single query if possible.

Answer 1

Not very nice, but I think this is the best you can do in MySQL.

SELECT p.pID, p.pName
   FROM (
       SELECT (
           SELECT pID FROM products
           WHERE pCategory=p.pCategory AND pDisplay=1 AND pFeatured=1
           ORDER BY rand() LIMIT 1
           ) AS pID
       FROM products p
       WHERE pDisplay=1 AND pFeatured=1
       GROUP BY pCategory
       ORDER BY rand() LIMIT 4
       ) c
   JOIN products p ON c.pID = p.pID

(karim79's query can return multiple products from the same category, possibly even products with pDisplay=0 or pFeatured=0. rexem's query handles the pDisplay/pFeatures issue, but can also return multiple products from the same category)