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

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 type='payment' AND concept!='4' AND 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?

CodePudding user response：

select id
      ,user 
      ,concept  
      ,date 
      ,type 
      ,confirmed
from  (
       select *
              ,row_number() over(partition by user order by id desc) as rn
       from   t
       where  confirmed = 1
       ) t
where  rn = 1

id	user	concept	date	type	confirmed
32189	107	3	2022-08-12 00:00:00	payment	1
28446	8408	3	2022-03-11 00:01:00 40	payment	1

Fiddle

CodePudding user response：

You may also try the following:

Select T.id, T.user, T.concept, T.date, T.type, T.confirmed 
From
movements T 
Join 
(
  Select MAX(id) lastid, user
  From movements
  Where user IN (107, 8408) And type='payment' And concept!='4' And confirmed
  Group By user
) D
On T.id = D.lastid

See a demo.