-1

I have a row with products and I'd like to get 10 random records, but a maximum of 1 row per user_id. Right now I have this:

SELECT user_id, product_id, price, name, category, is_featured 
FROM db_products
WHERE category!=15 AND active=1 AND deleted=0 AND is_featured=1 
ORDER BY RAND() DESC LIMIT 0,12

I tried doing a SELECT DISTINCT user_id, ... but that doesn't work. The table has 100's of products and each user_id may have multiple ones, but I'd like to retrieve a maximum of 1 per user_id, but still a total of 10.

Is that possible at all without a more complex structure?

user1227914
  • 3,446
  • 10
  • 42
  • 76

2 Answers2

3

I may be missing something, but have you tried doing a GROUP BY?

SELECT user_id, product_id, price, name, category, is_featured 
FROM db_products
WHERE category!=15 AND active=1 AND deleted=0 AND is_featured=1 
GROUP BY user_id -- SPECIFY FIELD HERE
ORDER BY RAND() DESC 
LIMIT 0,12

This will group one row per user, or whichever field you desire to group by.

Bryan
  • 6,682
  • 2
  • 17
  • 21
  • That worked, thank you so much! Will accept your answer as the first one in 5 minutes :) – user1227914 Oct 04 '15 at 18:51
  • 1
    This is not truly random solution. You're first grouping and then randomizing order, which means, that you're randomizing order of users but NOT values for them and while grouping ONLY the first value in DB order will be fetched. Try to execute it few times and you'll see. Here is example in SQL fiddle: http://sqlfiddle.com/#!9/2b24b/3 – Grzegorz Adam Kowalski Oct 04 '15 at 19:09
2

Try something like that with grouping in main query after random ordering in subquery:

SELECT * FROM
(SELECT user_id, product_id, price, name, category, is_featured 
FROM db_products
WHERE category!=15 AND active=1 AND deleted=0 AND is_featured=1 
ORDER BY RAND()) AS subquery
GROUP BY user_id
LIMIT 0,10
Grzegorz Adam Kowalski
  • 5,243
  • 3
  • 29
  • 40