Geek Answers Handbook

Two-column MYSQL Max Day Group

I want to get the latest creation date between the sender and the recipient in my table, where the sender or recipient = 002, and then the group is the sender and recipient. At the end, indicate whether the item is the sender or receiver in terms of 002.

This is just an example of 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 that 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 am trying to get the last current row, but not a problem with create_date.

2) I also tried using the inner join.

 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 getting the last row entry. But what if I need to get the rows with the last create_date, but group by sender and receiver?

+6
source share
3 answers

Perhaps use an additional query to get a list of members and types with dates, and then get the maximum of them: -

 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

Identifier is a minor issue. If necessary, he needs to join the table based on the date of member / type / create to get it, or make 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
+1
source

You can do it with one click, 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
+2
source

Here's a terribly inefficient way to do this, but it will get the right 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';

The internal sub-selection will find for you the last create_date for each member involved, and the external selection will find a specific row.

+1
source

More articles:


All Articles