MYSQL group by with last value but conditional

Question

I'll explain my scenario. Mysql 5.7

I have 2 table: registry and movement. This two table have IDMovement in common so i can join them

My problem: I need to GROUP BY the Registry column (is varchar column), i need to check the MAX IDMovement. In this row, there is another column called IDCausal. If on the MAX IDMovement the IDCasual is different to 21, then skip. Else return the row.

I'll make an example of what i have done:

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

P.S. The WHERE is just an example, i need to do the query for every Registry.

The return values for the registry 2SST0160 are:

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 case is a NOTHING. If the IDCausal was 21, the expected result is only the row with 21. For every Registry, i'll expect ZERO or ONE row as result for every registry.

I'll hope i was clear, thanks for the help!

Sorry, missed that you said that. Write a subquery that returns the row with MAX(IDMovement) for each `Registry`; see https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1. Join with that and test if the max is 21. — Barmar, Jan 10 '23 at 21:02
I have see that discussion, but mine isn't a single table but already is a join, so I don't know how to manage — g-pane, Jan 10 '23 at 21:35
See this one: https://stackoverflow.com/questions/33145885/sql-selecting-from-two-tables-with-inner-join-and-limit?noredirect=1&lq=1 — Barmar, Jan 10 '23 at 21:36
_"If on the MAX IDMovement the IDCasual is different to 21, then skip. Else return the row."_ -- `GROUP BY` and the SQL aggregate functions do not select rows. `GROUP BY` creates new rows using the values from a group of rows. The value in each column is computed separated and the final created row does not exist in the input table(s). This is a common mistake and misunderstanding about `GROUP BY`. Your need can be fulfilled but not by using `GROUP BY`. — axiac, Jan 11 '23 at 09:01
Learn from [this answer](https://stackoverflow.com/a/28090544/4265352), [this answer](https://stackoverflow.com/a/27802817/4265352), [this answer](https://stackoverflow.com/a/36579929/4265352), [this answer](https://stackoverflow.com/a/41410533/4265352) or [this answer](https://stackoverflow.com/a/30381339/4265352) on similar questions how to select the row that has the `MAX` value from its group on some column (without using `GROUP BY` because `GROUP BY` does a different thing). — axiac, Jan 11 '23 at 09:06

score 0 · Answer 1 · answered Jan 11 '23 at 08:55

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;

MYSQL group by with last value but conditional

1 Answers1