1

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 ?

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
amit gupta
  • 1,282
  • 2
  • 20
  • 36

2 Answers2

1

You could use ROW_NUMBER(MySQL 8.0):

SELECT *
FROM (SELECT l.*, c.comment_data, c.follow_up_time,
         ROW_NUMBER() OVER(PARTITION BY l.id ORDER BY c.follow_up_time DESC) AS rn
      FROM lms_leads l 
      LEFT join lms_leads_comments as c ON l.id=c.lead_id) s
WHERE rn = 1;

DBFiddle Demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

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;

Demo

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69