Reliable way of getting the max value of a column for each unique user id while meeting other criteria

Question

I'm trying to bulk-get all of the latest payments for a group of user ids, but I've been struggling with getting all of them under a unified query

I went with:

SELECT t1.*
FROM movements t1 LEFT JOIN movements t2
ON (t1.user = t2.user AND t1.id < t2.id)
WHERE t2.id IS NULL
AND t1.user IN ({$ids}) AND t1.type='payment' AND t1.concept!='4' AND t1.confirmed

...which worked to an extent, but some entries were being left out. I extended it to

ON (t1.user = t2.user AND t1.id < t2.id)
WHERE t2.id IS NULL AND t2.date IS NULL

and that yielded more results, but some of them weren't being selected still.

Here are two samples where the query will not yield anything

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
29755   107     3                   2022-06-12 00:01:00     payment     1
31257   107     3                   2022-07-12 00:00:00     payment     1
32189   107     3                   2022-08-12 00:00:00     payment     1         
32460   107     COMISSION BALANCE   2022-08-23 10:50:50     comission   

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
27298   8408    3                   2022-03-11 08:44:53 40  payment     
28446   8408    3                   2022-03-11 00:01:00 40  payment     1         
28447   8408    3                   2022-04-19 17:22:42 40  payment

Using the "crude" alternative does, obviously.

SELECT * FROM movements WHERE user=107 AND (etc) ORDER BY id DESC LIMIT 1

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
32189   107     3                   2022-08-12 00:00:00     payment     1
        

Since I have the ids, I could simply do a foreach for every $user and make a million individual calls, but I'd rather avoid that.

I can tell there's something off with the "topmost" rows not meeting the full criteria, but I have no clue on how to tell SQL to get me the ones that do.

How could I achieve this in a clean way?

Answer 1

To select the row with the highest id for each user that matches the additional criteria, even if there's a row with a higher id that doesn't match the additional criteria, you need to add to your on clause:

and t2.type='payment' AND t2.concept!='4' AND t2.confirmed