CODEWITHSUNDEEP
mysql
Ariful Islam
Ariful Islam

Reputation: 7675

Getting last row of each group in mysql

I have two tables as following:

messages

+----+--------+-----------------+
| id | sender | body            |
+----+--------+-----------------+
| 11 | 4      | test msg one    |
| 12 | 4      | test msg        |
| 13 | 1      | this is test    |
| 14 | 4      | WANT TO SHOW G1 |
| 15 | 4      | WANT TO SHOW G2 |
+----+--------+-----------------+

message_receivers

+----------+------------+
| receiver | message_id |
+----------+------------+
| 1        |         11 |
| 1        |         12 |
| 4        |         13 |
| 1        |         14 |
| 3        |         15 |
+----------+------------+

I have written following query

SELECT 
    `messages`.`id`,
    `messages`.`body` ,
    `messages`.`sender`, 
    MAX(`messages`.`id`) AS MID,
    IF(`messages`.`sender`>`message_receivers`.`receiver`,`CONCAT_WS`(',',`messages`.`sender`,`message_receivers`.`receiver`), 

`CONCAT_WS`(',',`message_receivers`.`receiver`,`messages`.`sender`)) AS `conc`
FROM
    `messages`
JOIN `message_receivers` ON `messages`.`id` = `message_receivers`.`message_id`
WHERE 
    `message_receivers`.`receiver` = '4' 
    OR `messages`.`sender` = '4'
GROUP BY conc

which give me the following result

+----+-----------------+--------+------+------+
| id | body            | sender | MID  | conc |
+----+-----------------+--------+------+------+
| 11 | test msg one    | 4      |   14 | 4,1  |
| 15 | WANT TO SHOW G2 | 4      |   15 | 4,3  |
+----+-----------------+--------+------+------+

But I want 

+----+-----------------+--------+------+------+
| id | body            | sender | MID  | conc |
+----+-----------------+--------+------+------+
| 14 | WANT TO SHOW G1 | 4      |   14 | 4,1  |
| 15 | WANT TO SHOW G2 | 4      |   15 | 4,3  |
+----+-----------------+--------+------+------+

What should I do? Thanks in advance.

Upvotes: 0

Views: 49

Answers (2)

Strawberry
Strawberry

Reputation: 33935

The standard solution is to use an uncorrelated subquery, as follows:

SELECT x.* 
  FROM my_table
  JOIN 
     ( SELECT grouping_column
            , MAX(ordering_column) max_ordering_column 
         FROM my_table 
        GROUP 
           BY grouping_column
     ) y
    ON y.grouping_column = x.grouping_column
   AND y.max_ordering_column = x.ordering_column;

or, where a key is formed on multiple columns...

SELECT x.* 
  FROM my_table
  JOIN 
     ( SELECT grouping_column1
            , grouping_column2
            , MAX(ordering_column) max_ordering_column 
         FROM my_table 
        GROUP 
           BY grouping_column1
            , grouping_coulmn2
     ) y
    ON y.grouping_column1 = x.grouping_column1
   AND y.grouping_column2 = x.grouping_column2
   AND y.max_ordering_column = x.ordering_column;

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Using a sub query to get the max message, and then joining that back to get the other fields:-

SELECT messages.id, messages.body, messages.sender, Sub1.MID, Sub1.conc
FROM messages
INNER JOIN message_receivers 
ON messages.id = message_receivers.message_id
INNER JOIN
(
    SELECT 
        IF(messages.sender>message_receivers.receiver,
            CONCAT_WS(',',messages.sender,message_receivers.receiver), 
            CONCAT_WS(',',message_receivers.receiver,messages.sender)) AS conc,
        MAX(messages.id) AS MID
    FROM messages
    JOIN message_receivers 
    ON messages.id = message_receivers.message_id
    WHERE message_receivers.receiver = '4' 
    OR messages.sender = '4'
    GROUP BY conc
) Sub1
ON Sub1.MID = messages.id
AND Sub1.conc = IF(messages.sender>message_receivers.receiver,
            CONCAT_WS(',',messages.sender,message_receivers.receiver), 
            CONCAT_WS(',',message_receivers.receiver,messages.sender))

SQL fiddle for it - http://sqlfiddle.com/#!2/8ee98/2

Upvotes: 1

Related Questions