MySQL query with a Max row value with multiple conditions

Question

I have a table like this

gen_loc	rev_number	status	action
5A2	09	PROCESSED	INSERT
5A2	10	PROCESSED	INSERT
5A2	10	PROCESSED	DELETE
8A5	09	PROCESSED	INSERT
8A5	10	PROCESSED	UPDATE
10A6	09	PROCESSED	INSERT
10A6	10	PROCESSED	DELETE

I am trying to select the rows which contains the MAX value from rev_number column BUT ONLY IF THIS COLUMN ITS THE MAX VALUE WITH ACTION DIFFERENT TO DELETE AND EQUAL TO PROCESSED

As you can see, in the gen_loc 10A6 we have 10 as a max value but action delete so, I expect that the query doesn't show the gen_loc 10A6 for this case. I have been trying other queries using MAX(rev_number) but it shows me still the gen_loc with 09 rev_number.

I expect this result:

gen_loc	rev_number	status	action
5A2	10	PROCESSED	INSERT
8A5	10	PROCESSED	UPDATE

How to select a Max value with the condition that the action IS NOT DELETE AND STATUS EQUAL TO PROCESSED? Thank you!

Answer 1

Check out this db fiddle

If you don't want to group by any column, you can do in this way

SELECT tbl.* 
FROM tbl
JOIN
(
    SELECT max(rev_number) as t2_revNum
     FROM tbl
) t2 ON tbl.rev_number=t2.t2_revNum
WHERE status = 'PROCESSED' AND action <> 'Delete'