Select random rows, but without duplicate values ​​from one column

Question

This is a simple selection from a single table. The goal is to select four random products, one from each of the x categories, with a few "where" restrictions. I tried this:

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

This type of work, but it always returns the same product from any category. I want to change the displayed products while showing only one product for any category.

I also tried:

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

I think maybe he needs two choices - first, to get random 4 categories, to select a random row from each of them, but a. I'm not sure how to do this, and b. would prefer to use one request, if possible.

Answer 1

, , , 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 , pDisplay = 0 pFeatured = 0. rexem query pDisplay/pFeatures, )