CODEWITHSUNDEEP
phpmysql
vazgen
vazgen

Reputation: 83

How select multiple maximum values with mysql

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 !

Upvotes: 0

Views: 667

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 1

user3380417
user3380417

Reputation: 61

Haven't tried this but group by should work. Something like:

query = "SELECT MAX(thread_id) FROM recipients GROUP BY user_id WHERE user_id IN ()"

Upvotes: 0

Related Questions