CODEWITHSUNDEEP
mysqlgroup-bymax
user2126081
user2126081

Reputation: 285

MYSQL Max date group by two columns

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?

Upvotes: 7

Views: 2113

Answers (3)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 1

AdrianBR
AdrianBR

Reputation: 2588

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

Upvotes: 2

vhu
vhu

Reputation: 12788

Here is a horribly ineffective way to do it, but it'll achieve correct results:

SELECT id, t.member, COALESCE(IF(sender<>2,'sender',NULL),'receiver') type, t.create_date 
FROM a
JOIN (
 SELECT COALESCE(IF(sender<>2,sender,NULL),receiver) member, MAX(create_date) create_date 
 FROM a GROUP BY member
) t on ((a.sender=t.member or a.receiver=t.member) AND a.create_date=t.create_date)
WHERE a.sender='002' OR a.receiver='002';

Inner sub-select will find you latest create_date for each involved member and outer select will find that specific row.

Upvotes: 1

Related Questions