MySQL 8.0

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

CREATE TABLE products (
  `id` INTEGER,
  `code` INTEGER,
  `active` INTEGER
);

INSERT INTO products
  (`id`, `code`, `active`)
VALUES
  ('1', '1111', '0'),
  ('2', '1111', '0'),
  ('3', '1111', '1'),
  ('4', '2222', '0'),
  ('5', '2222', '1'),
  ('6', '2222', '0'),
  ('7', '3333', '0'),
  ('8', '3333', '0'),
  ('9', '3333', '0');
select coalesce(min(case when active = 1 then id end), min(id)) id, code, max(active) active
from products
group by code


id
code
active


3
1111
1

5
2222
1

7
3333
0


select p.id, p.code, p.active
from (
  select *, row_number() over (partition by code order by active desc, id) rn
  from products
) p  
where p.rn = 1  


id
code
active


3
1111
1

5
2222
1

7
3333
0