MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Adminer 4.7.7 MySQL dump

SET NAMES utf8;

SET time_zone = '+00:00';

SET foreign_key_checks = 0;

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (

`id` tinyint unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `category` (`id`, `title`) VALUES

(1, 'Fruit'),

(2, 'Car'),

(3, 'City');

DROP TABLE IF EXISTS `item`;

CREATE TABLE `item` (

`id` tinyint NOT NULL AUTO_INCREMENT,

`category_id` tinyint unsigned NOT NULL,

`name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`),

KEY `category_id` (`category_id`),

CONSTRAINT `item_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `item` (`id`, `category_id`, `name`) VALUES

(1, 1, 'Banana'),

(2, 1, 'Mango'),

(3, 1, 'Apple'),

(4, 1, 'Watermelon'),

(5, 1, 'Pear'),

(6, 2, 'Audi'),

SELECT id, name, title

FROM

(

SELECT i.id, i.name, c.title,

DENSE_RANK() OVER (PARTITION BY i.category_id ORDER BY i.id DESC ) AS dr

FROM category c

JOIN item i

ON i.category_id = c.id

) t

WHERE dr <= 3

ORDER BY t.id DESC