MySQL 8.0

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

CREATE TABLE tablename (
  `employee_email` VARCHAR(14),
  `status` VARCHAR(10),
  `id` INTEGER
);

INSERT INTO tablename
  (`employee_email`, `status`, `id`)
VALUES
  ('test1@test.com', 'active', '1'),
  ('test1@test.com', 'terminated', '2'),
  ('test2@test.com', 'active', '3'),
  ('test3@test.com', 'terminated', '4'),
  ('test4@test.com', 'terminated', '5'),
  ('test4@test.com', 'terminated', '6');
SELECT t.employee_email, t.status, t.id
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_email ORDER BY status = 'active' DESC, id) rn
  FROM tablename
) t
WHERE t.rn = 1


employee_email
status
id


test1@test.com
active
1

test2@test.com
active
3

test3@test.com
terminated
4

test4@test.com
terminated
5


SELECT t.employee_email, MAX(t.status) status, MIN(t.id) id
FROM tablename t
WHERE t.status = 'active'
OR NOT EXISTS (SELECT 1 FROM tablename WHERE employee_email = t.employee_email AND status = 'active')
GROUP BY t.employee_email


employee_email
status
id


test1@test.com
active
1

test2@test.com
active
3

test3@test.com
terminated
4

test4@test.com
terminated
5

employee_email	status	id
test1@test.com	active	1
test2@test.com	active	3
test3@test.com	terminated	4
test4@test.com	terminated	5