aLvEe aLvEe - 10 days ago 5
MySQL Question

MySQL - Get record by category randomly and also have limit of every category

Still i am querying this for getting the result with 3 specific categories

SELECT
id,
title,
category
FROM
contents
WHERE
category IN ('kids', 'adult', 'family')
LIMIT 10


enter image description here

But how i get the 4 kids, 3 adult and 3 family categories type row from our records like have in screenshot

enter image description here

please help me to solve this issue, there is dummy data and table structure. Thanks in advance

-- ----------------------------
-- Table structure for content
-- ----------------------------
DROP TABLE IF EXISTS `content`;
CREATE TABLE `content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`category` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of content
-- ----------------------------
INSERT INTO `content` VALUES ('1', 'a', 'kids');
INSERT INTO `content` VALUES ('2', 'b', 'kids');
INSERT INTO `content` VALUES ('3', 'c', 'kids');
INSERT INTO `content` VALUES ('4', 'd', 'kids');
INSERT INTO `content` VALUES ('5', 'e', 'kids');
INSERT INTO `content` VALUES ('6', 'f', 'kids');
INSERT INTO `content` VALUES ('7', 'g', 'kids');
INSERT INTO `content` VALUES ('8', 'h', 'kids');
INSERT INTO `content` VALUES ('9', 'i', 'kids');
INSERT INTO `content` VALUES ('10', 'j', 'kids');
INSERT INTO `content` VALUES ('11', 'k', 'family');
INSERT INTO `content` VALUES ('12', 'l', 'family');
INSERT INTO `content` VALUES ('13', 'm', 'family');
INSERT INTO `content` VALUES ('14', 'n', 'family');
INSERT INTO `content` VALUES ('15', 'n', 'adult');
INSERT INTO `content` VALUES ('16', '0', 'adult');
INSERT INTO `content` VALUES ('17', 'p', 'adult');
INSERT INTO `content` VALUES ('18', 'q', 'adult');
INSERT INTO `content` VALUES ('19', 'r', 'adult');
INSERT INTO `content` VALUES ('20', 's', 'adult');

Answer

Limit each category separately and UNION the results.

SELECT * FROM (
  SELECT id, title, category
      FROM content
      WHERE category = 'kids'
      LIMIT 4
) kids
UNION SELECT * FROM (
  SELECT id, title, category
    FROM content
    WHERE category = 'adult'
    LIMIT 3
) adult
UNION SELECT * FROM (
  SELECT id, title, category
    FROM content
    WHERE category = 'family'
    LIMIT 3
) family

Here's the Fiddle.

Comments