MySQL - Selecting single entry per category

Question

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

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Marc B, Feb 17 '14 at 00:30
Look at function `RAND` use `GROUP BY` https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand — Mihai, Feb 17 '14 at 00:32
Thanks Marc B. Actually mine is kind of different, my whole scenario is: I have an Inventory (SQL); I used 2 serial numbers, item number and category number. I have around 1000 items and around 750 categories. I need to do a loop in PHP to display all the category and I only need to display a random Item per category. I don't think I can use "UNION ALL".. and yeah, changing the inventory format is not an option — vise, Feb 17 '14 at 00:38
It's a good design to create a separate table called Categories — kta, Feb 17 '14 at 01:01
yes I agree Kazi Tanvir Ahsan.. but Im having a bit of confusion on how to apply Peterm solution — vise, Feb 17 '14 at 06:12

score 3 · Accepted Answer · answered Feb 17 '14 at 00:38

3

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

answered Feb 17 '14 at 00:38

peterm

91,357
15
148
157

Hi Peterm, this looks great! I will try this and post an update later. Thanks very much! – vise Feb 17 '14 at 00:49
Awesome! that works like charm,even with out joining second table. – Gowri Naidu R Apr 18 '14 at 11:06

Rafa Paez · Answer 2 · 2014-02-17T00:58:25.787

0

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

edited Feb 17 '14 at 00:58

answered Feb 17 '14 at 00:51

Rafa Paez

4,820
18
35

This relies on MySQL group by extensions that are explicitly documented as unsafe: http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html. – Gordon Linoff Feb 17 '14 at 02:23
It looks a bit similar to Peterm.. I want to stick with Peterm solution. Thanks anyway – vise Feb 17 '14 at 06:14

score 0 · Answer 3 · answered Feb 17 '14 at 02:22

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;

Hi, I think this will work too however I want to stick with Peterm solution. Thanks anyway — vise, Feb 17 '14 at 06:13

score 0 · Answer 4 · answered Feb 18 '14 at 09:39

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()

MySQL - Selecting single entry per category

4 Answers4