MySQL 8.0

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

CREATE TABLE `product` (
  `product_id` int(5) NOT NULL,
  `name` varchar(64) NOT NULL,
  `group` varchar(64) NOT NULL,
  `quantity` varchar(5) NOT NULL,
  `sort` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `product` (`product_id`, `name`, `group`, `quantity`, `sort`) VALUES
(1, 'Товар 1', '', '5', '0'),
(2, 'Товар 2', '', '0', '0'),
(3, 'Товар 3', 'top', '1', '1'),
(4, 'Товар 4', 'top', '0', '2'),
(5, 'Товар 5', 'top', '15', '3'),
(6, 'Товар 6', '', '2', '0'),
(7, 'Товар 7', '', '10', '0'),
(8, 'Товар 8', 'sale', '0', '0'),
(9, 'Товар 9', 'sale', '12', '0'),
(10, 'Товар 10', '', '0', '0');

SELECT * FROM product;


product_id
name
group
quantity
sort


1
Товар 1

5
0

2
Товар 2

0
0

3
Товар 3
top
1
1

4
Товар 4
top
0
2

5
Товар 5
top
15
3

6
Товар 6

2
0

7
Товар 7

10
0

8
Товар 8
sale
0
0

9
Товар 9
sale
12
0

10
Товар 10

0
0


WITH 
cte AS ( SELECT *, 
                ROW_NUMBER() OVER (PARTITION BY CASE `group` 
                                                WHEN '' THEN RAND()
                                                        ELSE `group`
                                                        END
                                   ORDER BY sort, product_id DESC) rn
         FROM product )
SELECT product_id, name, `group`, quantity, sort
FROM cte
WHERE rn = 1
ORDER BY product_id;


product_id
name
group
quantity
sort


1
Товар 1

5
0

2
Товар 2

0
0

3
Товар 3
top
1
1

6
Товар 6

2
0

7
Товар 7

10
0

9
Товар 9
sale
12
0

10
Товар 10

0
0


SELECT *
FROM product
WHERE `group` = ''
UNION ALL
SELECT t1.*
FROM product t1
JOIN ( SELECT MIN(product_id) product_id
       FROM product
       WHERE `group` != ''
         AND quantity > 0
       GROUP BY `group` ) t2 USING (product_id)
ORDER BY product_id;


product_id
name
group
quantity
sort


1
Товар 1

5
0

2
Товар 2

0
0

3
Товар 3
top
1
1

6
Товар 6

2
0

7
Товар 7

10
0

9
Товар 9
sale
12
0

10
Товар 10

0
0

product_id	name	group	quantity	sort
1	Товар 1		5	0
2	Товар 2		0	0
3	Товар 3	top	1	1
4	Товар 4	top	0	2
5	Товар 5	top	15	3
6	Товар 6		2	0
7	Товар 7		10	0
8	Товар 8	sale	0	0
9	Товар 9	sale	12	0
10	Товар 10		0	0