MYSQL Max date group by two columns

Question

I want to get the latest create date between sender and receiver in my table where sender or receiver = 002 and then group by sender and receiver. At the end notify whether member is sender or receiver from the view of 002.

This is just a sample data.

table

id    sender    |    receiver    |       create_date
1      001              002           2015-10-13 10:30:01  
2      003              002           2015-11-06 15:30:59
3      001              002           2015-11-02 05:30:01
4      001              002           2015-11-03 11:08:22
5      002              004           2015-10-20 12:15:36
6      002              004           2015-11-03 17:35:10
7      002              005           2015-09-01 06:02:20
8      002              001           2015-11-06 15:10:32 

---

The result should look like this

id    |    member    |    type    |       create_date
2           003          sender       2015-11-06 15:30:59
6           004         receiver      2015-11-03 17:35:10
7           005         receiver      2015-09-01 06:02:20
8           001         receiver      2015-11-06 15:10:32

---

So far I have tried:

1)

SELECT
    id,
    IF(sender <> '002', sender, receiver) AS member, 
    IF(sender <> '002', 'sender', 'receiver') AS type, 
    MAX(create_date) AS max_date
FROM
    table
WHERE
    sender = '002' OR receiver = '002'
GROUP BY
    member

This is the result i got at the end.

id    |    member    |    type    |       create_date
1           001          sender       2015-11-06 15:10:32
2           003          sender       2015-11-06 15:30:59
5           004         receiver      2015-11-03 17:35:10
7           005         receiver      2015-09-01 06:02:20

I struggle to get the latest current row but no problem with create_date.

2) I have tried using inner join as well.

SELECT
    *
FROM
    table a
INNER JOIN
(
    SELECT
        id,
        MAX(create_date) as max_date
    FROM
        table
    WHERE
        sender = '002' OR receiver = '002'        
) b ON b.max_date = a.create_date

This works well for retrieving latest row record only. But what if I need to retrieve rows with latest create_date but group by sender and receiver?

Answer 1

You can do it in one single select, with a case

select 
case when sender='002' then receiver else sender end as member, -- # knowing that either sender or receiver are 002, take the other.
case when sender='002' then 'receiver' else 'sender' end as type,
max(date)
from table
where sender = '002' or receiver = '002'
group by 1,2

Answer 2

Possibly use a sub query to get the list of members and types with the dates, then get the max from that:-

SELECT member, type, MAX(create_date)
FROM
(
    SELECT sender AS member, 'sender' AS type, create_date
    FROM table
    WHERE receiver = '002'
    UNION
    SELECT receiver AS member, 'receiver' AS type, create_date
    FROM table
    WHERE sender = '002'
) sub0
GROUP BY member, type

The id is a minor issue. If required then it needs to join back against the table based on the member / type / create date to get it, or do a fiddle with GROUP_CONCAT:-

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY create_date DESC), ',', 1), 
        member, 
        type, 
        MAX(create_date)
FROM
(
    SELECT id, sender AS member, 'sender' AS type, create_date
    FROM table
    WHERE receiver = '002'
    UNION
    SELECT id, receiver AS member, 'receiver' AS type, create_date
    FROM table
    WHERE sender = '002'
) sub0
GROUP BY member, type