Devesh Agrawal Devesh Agrawal - 1 year ago 74
MySQL Question

How to select last order date for each customer in mysql

table_customers(customer_id, customer_name)
table_orders(customer_id, order_id, order_datetime)

I want to get
last order date
for each
. If customer has not placed any order return
for her.

This is my query.

select C.customer_id , date(O.order_datetime)
from table_customers C
INNER JOIN table_orders O ON C.customer_id = O.customer_id
group by O.customer_id order by O.order_datetime desc limit 1;

It is returning last order date for for last customer only.

How to get last order date for all the customers?

Answer Source

Hi checkout my sql below..

select coalesce(max(o.order_datetime), '0000-00-00 00:00:00') as last_order_date, c.customer_id 
from table_orders as o
right join table_customers as c on o.customer_id = c.customer_id
group by c.customer_id
order by c.customer_id;

This might help you.

The sample results are given below.

enter image description here