MySQL 5.7

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

create table Product (id int auto_increment primary key,
                       name varchar(20),
                       product_category varchar(20),
                       price decimal(10,2));
insert into Product (name, product_category, price) values
('chair', 'furniture', 20.5),
('table', 'furniture', 108),
('bed', 'furniture', 220),
('sofa', 'furniture', 134),
('coat', 'clothing', 55.20),
('tie', 'clothing', 20),
('shirt', 'clothing', 35.99)

Records: 7  Duplicates: 0  Warnings: 0


SELECT p1.product_category, p1.name, p1.price, COUNT(*) AS row_number
FROM Product p1
JOIN Product p2 ON p2.product_category = p1.product_category AND p2.price <= p1.price
GROUP BY p1.product_category, p1.price, p1.name
ORDER BY p1.product_category, row_number


product_category
name
price
row_number


clothing
tie
20.00
1

clothing
shirt
35.99
2

clothing
coat
55.20
3

furniture
chair
20.50
1

furniture
table
108.00
2

furniture
sofa
134.00
3

furniture
bed
220.00
4

product_category	name	price	row_number
clothing	tie	20.00	1
clothing	shirt	35.99	2
clothing	coat	55.20	3
furniture	chair	20.50	1
furniture	table	108.00	2
furniture	sofa	134.00	3
furniture	bed	220.00	4