如何使用mysql选择多个最大值

Question

I have a table recipients like this

Id    user_id   thread_id
_________________________
1     1215      3833
2     1215      3833
3     1862      3833
........................

10    1215      3834
11    1862      3834

And I have an array with some values(user_id). For each value, which is my user_id-s I want to select maximum thread_id. For single given value(for example 1215) I can get the value with this simple query

query = "SELECT MAX(thread_id) FROM recipients WHERE user_id = 1215 "

But I can't use this to get all maximum thread_id-s from table when I will try to write something like this

query = "SELECT MAX(thread_id) FROM recipients WHERE user_id IN (1215,1862,1799)"

I want to know is it possible to get all maximum values for given array with single query (without using for or foreach and run query for every value of array). Thanks !

Answer 1

You need to use GROUP BY using aggregate function you should group them ,for the first query there is where clause for one user so it will give your the max thread id but in second query you have 3 user ids so you need to group them to get 3 max thread ids per user

SELECT MAX(thread_id) 
FROM recipients 
WHERE user_id IN (1215,1862,1799)
 GROUP BY user_id 

fiddle demo

GROUP BY (Aggregate) Functions