0

Can someone point me to the right direction what i need to read? ...

I have two tables. 1st with customer id and customer name. 2nd with customer id and date of call to customer. I used LEFT JOIN to get a list for each customern with all calls to him. Like this:

1, Max Mustermann, 2019-05-22
1, Max Mustermann, 2019-05-20 (<- I don't want this row to appear.)
2, Ilse Meier,

I used LEFT JOIN to get customers without calls too.

Now I want this result with only the latest call.

For one call (to get the latest) I could do:

SELECT * FROM calls ORDER BY call_date DESC LIMIT 1;

I'm stuck. What do I need to read about?

M. Kanarkowski
  • 2,155
  • 8
  • 14
jkabutz
  • 15
  • 5

1 Answers1

1

You can use group by and max() to get a customer with the newest call. When a customer has never called there will be NULL in Maxdateofcall.

select 
     t1.customerid
    ,t1.customername
    ,max(t2.dateofcall) as Maxdateofcall
from table1 t1 left join table1 t2 on t1.customerid = t2.customerid
group by t1.customerid ,t1.customername


1, Max Mustermann, 2019-05-22
2, Ilse Meier, null
M. Kanarkowski
  • 2,155
  • 8
  • 14