This tutorial shows how to select items randomly from each category in MySQL. So here I will select exactly one item randomly from each category. People who need sometimes to select photo from each photo category and display them on the page for animation purpose will be benefited.

In this example I have used two sample tables with data. You can change the name and column names as per your needs and you will get the same results.

Prerequisites

MySQL 5.x/8.x

Tables

MySQL 5.x

First create item category table which holds the category for items:

CREATE TABLE item_categories (
    category_id     int(10) unsigned NOT NULL AUTO_INCREMENT,
    category_name   VARCHAR(50),
    category_date   timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (category_id)
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;

Create table items which holds items from each category above:

CREATE TABLE items (
    item_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    category_id    int NOT NULL,
    item_name VARCHAR(50) ,
    item_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (item_id)
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;

MySQL 8.x

Here are the tables for MySQL 8.x version:

CREATE TABLE item_categories (
    category_id     int unsigned NOT NULL AUTO_INCREMENT,
    category_name   VARCHAR(50),
    category_date   timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (category_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE items (
    item_id int unsigned NOT NULL AUTO_INCREMENT,
    category_id    int NOT NULL,
    item_name VARCHAR(50) ,
    item_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (item_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table Data

I am going to insert some sample data for the sake of this example. The real application will have different set of data.

INSERT INTO item_categories VALUES (1, 'cat1', '2014-04-11 08:42:06');
INSERT INTO item_categories VALUES (2, 'cat2', '2014-04-11 08:42:07');
INSERT INTO item_categories VALUES (3, 'cat3', '2014-04-11 08:42:08');
INSERT INTO item_categories VALUES (4, 'cat4', '2014-04-11 08:42:09');
INSERT INTO item_categories VALUES (5, 'cat5', '2014-04-11 08:42:10');

INSERT INTO items VALUES (1, 1, 'item1', '2014-04-11 08:43:06');
INSERT INTO items VALUES (2, 1, 'item2', '2014-04-11 08:43:07');
INSERT INTO items VALUES (3, 2, 'item3', '2014-04-11 08:43:08');
INSERT INTO items VALUES (4, 2, 'item4', '2014-04-11 08:43:09');
INSERT INTO items VALUES (5, 3, 'item5', '2014-04-11 08:43:10');
INSERT INTO items VALUES (6, 3, 'item6', '2014-04-11 08:43:11');
INSERT INTO items VALUES (7, 4, 'item7', '2014-04-11 08:43:12');
INSERT INTO items VALUES (8, 4, 'item8', '2014-04-11 08:43:13');
INSERT INTO items VALUES (9, 5, 'item9', '2014-04-11 08:43:14');
INSERT INTO items VALUES (10, 5, 'item10', '2014-04-11 08:43:15');

Random Selection

Now to get the random item from each category you can execute the following SQL query:

SELECT *
FROM item_categories
LEFT JOIN (SELECT * FROM items ORDER BY RAND())
tmp ON (item_categories.category_id = tmp.category_id)
GROUP BY tmp.category_id
ORDER BY item_categories.category_date;

The above query will run and produce results fine in MySQL 5.x but for MySQL 8.x you may face an issue with full mode group by clause. To solve this problem in MySQL 8.x please have a look at the solution – incompatible with sql_mode=only_full_group_by.

Re-executing the same query will give you the same results in MySQL 8.x also.

The final result you will see from the above query is:

That’s all about selecting single random item from each category.

Source Code

Download