CODEWITHSUNDEEP
mysqlsql-order-bydistinct
RussH
RussH

Reputation: 11

SQL: Return only the most recent activity per customer

I have two tables with linked data. There are activity updates against individual customers (customer_id). I want to return the most recent activity per customer;

contact; customer_id (auto_increment) last_name first_name
phone_work

activity; activity_id (auto_increment) data_item_id entered_by date_created notes

I can return the entire set of activities;

SELECT last_name, first_name, date_created, notes, FROM contact JOIN activity ON contact.customer_id=activity.data_item_id;

..but I only want the most recent activity per customer_name. If I use unique, it seems to return the first activity per customer_name and not the most recent. I'm sure it's extrememly simple but I've not found it yet. Thoughts?

Upvotes: 0

Views: 276

Answers (3)

Sagar Joon
Sagar Joon

Reputation: 1417

One more solution :

 select temp.* from
 ( 
  SELECT c.last_name last, c.first_name first, a.date_created, a.notes 
  FROM contact c JOIN activity a ON c.customer_id=a.data_item_id
  order by a.date_created desc
 ) temp
 group by temp.last, temp.first;

Upvotes: 0

Cl_3518233
Cl_3518233

Reputation: 25

WITH TempTbl AS ( SELECT MAX(activity_id) activity_id --as it is auto_increment, this should return the most recent record) ,Last_Name ,First_Name ,Date_Created ,Notes FROM Activity JOIN Contact ON Contact.Customer_Id=Activity.Data_Item_Id ) SELECT * FROM TempTbl

if you still have multipe records per customer, then suggestion will be to consider using RANK()OVER(PARTITION BY... to select most recent record

Upvotes: 0

Marki555
Marki555

Reputation: 6870

You can use MAX(date_created) to choose the correct row from the joined table. Otherwise MySQL would join all the rows, or in case of GROUP BY, choose the first matching row it finds.

So the query would be:

SELECT MAX(date_created) AS date, last_name, first_name, notes
       FROM contact JOIN activity ON contact.customer_id=activity.data_item_id
       ORDER BY customer_id ASC

Upvotes: 0

Related Questions