MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE categories (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16) NOT NULL,
    id_greatness INT(4) UNSIGNED DEFAULT 1 NOT NULL,
    type ENUM ('PRODUCT', 'STORE', 'ALL') DEFAULT 'ALL' NOT NULL,
    CONSTRAINT id_UNIQUE UNIQUE (id)
);

CREATE INDEX fk_id_greatness_to_greatnesses_idx ON categories (id_greatness);

CREATE TABLE stores_categories (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_store BIGINT(16) UNSIGNED NOT NULL,
    id_category INT(11) UNSIGNED NOT NULL,
    CONSTRAINT id_UNIQUE UNIQUE (id),
    CONSTRAINT fk_associative_category_to_categories FOREIGN KEY (id_category) REFERENCES categories (id)
);

CREATE INDEX fk_associative_category_to_categories_idx ON stores_categories (id_category);



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


INSERT INTO categories (id, name, id_greatness, type) VALUES (1, 'Butcher', 1, 'STORE');
INSERT INTO categories (id, name, id_greatness, type) VALUES (2, 'Meat', 1, 'PRODUCT');
INSERT INTO categories (id, name, id_greatness, type) VALUES (3, 'Fish', 1, 'ALL');
INSERT INTO categories (id, name, id_greatness, type) VALUES (4, 'Cookies', 2, 'PRODUCT');
INSERT INTO categories (id, name, id_greatness, type) VALUES (5, 'Wines', 3, 'ALL');
INSERT INTO stores_categories (id, id_store, id_category) VALUES (1, 1, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (2, 2, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (3, 3, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (4, 4, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (5, 5, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (6, 6, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (7, 7, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (8, 8, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (9, 9, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (10, 10, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (11, 11, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (12, 12, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (13, 13, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (14, 14, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (15, 15, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (16, 16, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (17, 17, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (18, 18, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (19, 19, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (20, 20, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (21, 21, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (22, 22, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (23, 23, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (24, 24, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (25, 25, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (26, 26, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (27, 27, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (28, 28, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (29, 29, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (30, 30, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (31, 31, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (32, 32, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (33, 33, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (34, 34, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (35, 35, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (36, 36, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (37, 37, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (38, 38, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (39, 39, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (40, 40, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (41, 41, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (42, 42, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (43, 43, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (44, 44, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (45, 45, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (46, 46, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (47, 47, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (48, 48, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (49, 49, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (50, 50, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (51, 51, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (52, 52, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (53, 53, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (54, 54, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (55, 55, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (56, 56, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (57, 57, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (58, 58, 1);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (59, 59, 3);
INSERT INTO stores_categories (id, id_store, id_category) VALUES (60, 60, 1);
select *
from categories cat
where exists (
  select 1
  from stores_categories sc
  where sc.id_category = cat.id
)


id
name
id_greatness
type


1
Butcher
1
STORE

3
Fish
1
ALL


--
-- Emulated row_number via variables
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, rn
FROM
(
  SELECT sc.*
  , @rn := CASE 
           WHEN @cat_id = sc.id_category
           THEN @rn + 1 ELSE 1
           END AS rn
  , @cat_id := sc.id_category as cat_id
  FROM stores_categories sc
  CROSS JOIN (SELECT @cat_id:=0, @rn:=0) vars
  ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC
) sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
WHERE rn BETWEEN 1 AND 3
ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC


id
id_store
cat_name
cat_type
rn


60
60
Butcher
STORE
1

58
58
Butcher
STORE
2

56
56
Butcher
STORE
3

59
59
Fish
ALL
1

57
57
Fish
ALL
2

55
55
Fish
ALL
3


--
-- Grouped self-join
-- 
SELECT sc.id, sc.id_store
, cat.name AS cat_name
, cat.type AS cat_type
, COUNT(sc2.id_store) AS cnt
FROM stores_categories sc
LEFT JOIN stores_categories sc2
  ON sc2.id_category = sc.id_category 
 AND sc2.id_store >= sc.id_store
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
GROUP BY sc.id
HAVING cnt BETWEEN 1 AND 3
ORDER BY sc.id_category, sc.id_store DESC;


id
id_store
cat_name
cat_type
cnt


60
60
Butcher
STORE
1

58
58
Butcher
STORE
2

56
56
Butcher
STORE
3

59
59
Fish
ALL
1

57
57
Fish
ALL
2

55
55
Fish
ALL
3


--
-- Correlated sub-query
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, ( select count(*)
    from stores_categories sc2
    where sc2.id_category = sc.id_category 
      and sc2.id_store >= sc.id_store
  ) as rn
FROM stores_categories AS sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
HAVING rn BETWEEN 1 AND 3
ORDER BY sc.id_category, sc.id_store DESC;


id
id_store
cat_name
cat_type
rn


60
60
Butcher
STORE
1

58
58
Butcher
STORE
2

56
56
Butcher
STORE
3

59
59
Fish
ALL
1

57
57
Fish
ALL
2

55
55
Fish
ALL
3
id	id_store	cat_name	cat_type	rn
60	60	Butcher	STORE	1
58	58	Butcher	STORE	2
56	56	Butcher	STORE	3
59	59	Fish	ALL	1
57	57	Fish	ALL	2
55	55	Fish	ALL	3