MariaDB 10.6

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

select @@version;


@@version


10.6.9-MariaDB-1:10.6.9+maria~deb11


create table employees (employee_id int primary key,  employee_name varchar(255));
create table transactions (transaction_id int primary key,employee_id int, transaction_date date,transaction_type varchar(255), remarks varchar(255));
insert into employees values (1,'John Doe'),
(2,'Jane Doe'),
(3,'Teri Dactyl'),
(4,'Allie Grater');

insert into transactions values (1,1,'2021-03-28','Add','test 1'),
(2,3,'2022-09-07','Add','test 2'),
(3,2,'2019-08-01','Add','test 3'),
(4,4,'2023-06-05','Read','test 4'),
(5,4,'2023-05-12','Add','test 5'),
(6,2,'2020-02-01','Read','test 6'),
(7,3,'2022-11-15','Update','test 7'),
(8,1,'2020-06-14','Add','test 8'),
(9,1,'2020-01-14','Update','test 9'),
(10,2,'2023-12-31','Delete','test 10');

Records: 4  Duplicates: 0  Warnings: 0



Records: 10  Duplicates: 0  Warnings: 0


WITH cte
AS (
	SELECT t.transaction_id AS transaction_id
		,t.transaction_date AS transaction_date
		,row_number() OVER (
			PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
			) AS rn
		,e.employee_name AS employee_name
		,t.transaction_type AS transaction_type
		,t.remarks AS remarks
	FROM employees e
	JOIN transactions t ON t.employee_id = e.employee_id
	WHERE e.employee_name NOT IN (
			SELECT e.employee_name
			FROM employees e
			JOIN transactions t ON t.employee_id = e.employee_id
			WHERE transaction_type = 'Delete'
			)
	)
SELECT cte.transaction_id
	,cte.employee_name
  	,cte.transaction_date
	,cte.transaction_type
	,cte.remarks
FROM cte
WHERE rn = 1;


transaction_id
employee_name
transaction_date
transaction_type
remarks


4
Allie Grater
2023-06-05
Read
test 4

1
John Doe
2021-03-28
Add
test 1

7
Teri Dactyl
2022-11-15
Update
test 7

transaction_id	employee_name	transaction_date	transaction_type	remarks
4	Allie Grater	2023-06-05	Read	test 4
1	John Doe	2021-03-28	Add	test 1
7	Teri Dactyl	2022-11-15	Update	test 7