I have a table like this

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:

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

CodePudding user response：

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'

CodePudding user response：

Using GROUP BY and INNER JOIN can do it.

Note: If you want to get the expected result,the type of gen_loc column should take serious consideration, I think you need to store 9 instead of 09

SELECT t1.* 
FROM yourtable t1
JOIN
(
    SELECT max(rev_number) as maxNum,gen_loc
     FROM yourtable
    GROUP BY gen_loc
) t2 ON t1.gen_loc=t2.gen_loc AND t1.rev_number=t2.maxNum AND t1.action!='DELETE'

DB Fiddle Demo