Reputation: 23
My apologies. I have edited the below into 2 table, Im just having a bit of confusion.
I have a tables very similar as the ones below and I wanted to show all the table2-class 1 but only 1 random item per each table1-category
Sample Item Table1
+---------+---------------+---------------+ | ID | Item Name | Category | +---------+---------------+---------------+ | 01 | Item A | Cat 1 | | 02 | Item B | Cat 1 | | 03 | Item C | Cat 2 | | 04 | Item D | Cat 2 | | 05 | Item E | Cat 3 | | 06 | Item F | Cat 3 | +---------+---------------+---------------+
Sample Item Table2
+---------------+---------------+ | Category | Class | +---------------+---------------+ | Cat 1 | 1 | | Cat 2 | 1 | | Cat 3 | 2 | +---------------+---------------+
I wanted to show all the table2-class 1 but only 1 random item per each table1-category
Desired Result
+---------+---------------+---------------+ | 02 | Item B | Cat 1 | | 03 | Item C | Cat 2 | +---------+---------------+---------------+
(This is within my PHP script)
Thanks in advance
Upvotes: 1
Views: 223
Reputation: 23
Prior to the above edited scenario, I used the below query and it works fine except that it doesn't randomize the entry of each category:
SELECT * FROM Table1,Table2
WHERE Table2.Class = '1'
AND Table1.Category = Table2.Category
GROUP BY Table1.Category ORDER BY RAND()
Upvotes: 0
Reputation: 1271231
The safest way to do this is with a correlated subquery. To get the item_id
:
select category,
(select item_id from sample s where s2.category = s.category order by rand() limit 1) as item_id
from sample s
group by category;
To get the rest of the item information, join that back in:
select s.*
from (select category,
(select item_id from sample s where s2.category = s.category order by rand() limit 1) as item_id
from sample s
group by category
) c join
sample s
on s.item_id = c.item_id;
Upvotes: 0
Reputation: 4870
Try something like this:
SELECT id, itemname, category FROM (
SELECT id, itemname, category FROM sample_table
ORDER BY RAND()
) AS tmp
GROUP BY category
Note that this query is totally valid in MySQL http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html
Upvotes: 0
Reputation: 92845
You can do something like this
SELECT t.id, itemname, category
FROM
(
SELECT
(
SELECT id
FROM table1
WHERE category = t.category
ORDER BY RAND()
LIMIT 1
) id
FROM table1 t
GROUP BY category
) q JOIN table1 t
ON q.id = t.id
Note: using RAND()
is very costly
Output:
| ID | ITEMNAME | CATEGORY | |----|----------|----------| | 1 | Item A | Cat 1 | | 3 | Item C | Cat 2 | | 6 | Item F | Cat 3 |
Here is SQLFiddle demo
Upvotes: 3