Suppose I have the following two tables:
lead
id(PK) status assigned_to date
1 open Smith 2018-08-26
2 open Drew 2018-08-26
3 new Amit 2018-08-26
lead_comments
id lead_id(FK) comment_data follow_up_time
1 1 old task line2 2018-08-27 14:18:26
2 2 old task line1 2018-08-27 14:18:26
3 1 new task line1 2018-08-27 17:18:00
4 2 new task line3 2018-09-27 20:18:26
5 2 old task line2 2018-08-27 21:18:26
Now, I need an MySQL query that would select each lead matches with the latest comment (if present) Order By latest follow_up_time from the lead_comments table.
My Expected Result:
lead_id comment _id follow_up_time comment_data assigned_to
2 4 2018-09-27 20:18:26 new task line3 Drew
1 3 2018-08-27 17:18:00 new task line1 Smith
3 Null Null Null Amit
I am trying this:
SELECT l.id as lead_id,
l.status as status,
c.id as comment_id,
c.comment_date as comment_date,
c.comment_data as comment,
c.commented_by,
l.assigned_to
FROM lms_leads l
LEFT JOIN lms_leads_comments as c ON l.id=c.lead_id
JOIN (
SELECT max(cm.id) as id
FROM lms_leads_comments cm
GROUP BY cm.lead_id
) as cc ON c.id=cc.id
GROUP BY l.id
ORDER BY c.follow_up_time DESC
However, this query does not work as per my expected result
Please suggest how I can achieve what I'm trying to do ?
Try this (for MySQL version < 8.0):
select @rn := 1, @lead_id_lag := 0;
select l.id lead_id,
lc.id comment_id,
lc.follow_up_time,
lc.comment_data,
l.assigned_to
from lead l
left join (
select case when @lead_id_lag = lead_id then @rn := @rn + 1 else @rn := 1 end rn,
@lead_id_lag := lead_id,
id,
follow_up_time,
comment_data,
lead_id
from lead_comments
order by lead_id, follow_up_time desc
) lc on l.id = lc.lead_id and lc.rn = 1;