MYSQL group by last value but conditionally

Question

I will explain my situation. MySQL 5.7

I have 2 tables: registry and mobile. These two tables have IDMovement in common so I can join them

my question: I need to group by Registry column (which is a varchar column) and I need to check MAX IDMovement. In this row, there is another column named IDCausal. Skip if IDCasual on MAX IDMovement is different from 21. Otherwise return the row.

I'll give an example of what I did:

SELECT
    m.IDMovement,
    mo.IDCausal,
    m.Registry 
FROM
    registry m
    JOIN movement mo ON m.IDMovement = mo.IDMovement 
WHERE
    m.Registry = "2SST0160"

Note WHERE is just an example, I need to query each registry.

The return value of registry 2SST0160 is:

IDMovement   IDCausal  Registry
5550         21        2SST0160
9817         5         2SST0160

In the example, the MAX IDMovement is 9817, but the IDCausal is 5, so the expected result of the query in this example is NOTHING. If IDCausal is 21, the expected result is only rows of 21. For each registry, I expect the result to be 0 or 1 row per registry.

I hope I made it clear, thank you for your help!

Answer 1

WITH newtable AS ( SELECT
    m.IDMovement,
    mo.IDCausal,
    m.Registry
FROM
    registry m
    JOIN movement mo ON m.IDMovement = mo.IDMovement
WHERE
    m.Registry = '2SST0160' )
SELECT * FROM newtable
    WHERE (SELECT IDCausal FROM newtable ORDER BY IDMovement DESC LIMIT 1) = 21
    ORDER BY IDMovement DESC LIMIT 1;