CODEWITHSUNDEEP
mysqlsqldatabasejoin
user17
user17

Reputation: 331

Mysql: Get the rows with max value grouped by a field

Suppose I have a table messages like this:

id  sender  receiver  content  created_at
1    100       101    Hi        2015-12-01 
2    100       101    Hello     2015-12-02  
3    100       101    World     2015-11-02
4    103       101    Like      2015-11-05
5    103       101    Do        2015-11-04
6    105       102   ..................

With the receiver specified, I want to get the latest message and number of messages from each user, i.e. for user 101, I want to get:

2    100       101    Hello     2015-12-02     3
4    103       101    Like      2015-11-05     2

Is it possible to do that with a single statement or what's the most elegant way ?

Upvotes: 0

Views: 56

Answers (3)

Strawberry
Strawberry

Reputation: 33945

   SELECT x.*
        , y.total 
     FROM my_table x 
     JOIN 
        ( SELECT receiver
               , sender
               , MAX(created_at) max_created_at
               , COUNT(*) total 
            FROM my_table 
           GROUP 
              BY receiver
               , sender
        ) y 
       ON y.receiver = x.receiver 
      AND y.sender = x.sender 
      AND y.max_created_at = x.created_at
-- [WHERE receiver = 101]
        ;
+----+--------+----------+---------+------------+-------+
| id | sender | receiver | content | created_at | total |
+----+--------+----------+---------+------------+-------+
|  2 |    100 |      101 | Hello   | 2015-12-02 |     3 |
|  4 |    103 |      101 | Like    | 2015-11-05 |     2 |
|  6 |    105 |      102 | Re      | 2015-11-04 |     1 |
+----+--------+----------+---------+------------+-------+

http://www.sqlfiddle.com/#!9/3fef7/1

Upvotes: 0

Naruto
Naruto

Reputation: 4329

Idea here is to get latest record which can be acheived by ordering by created_at and then using group by to get the count.

SELECT a.id,a.sender,a.receiver,a.content,a.created_at,count(a.sender) from (Select  * from messages order by created_at desc) a  group by sender  ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Aggregate the information for 101 in a subquery, and then join that back to the original table:

select m.*, mm.maxca
from messages m join
     (select m2.receiver, max(m2.created_at) as maxca, count(*) as cnt
      from messages m2
      where m2.receiver = 101
     ) mm
     on m.receiver = mm.receiver and m.created_at = mm.created_at
where m.receiver = 101 ;

Upvotes: 1

Related Questions