CODEWITHSUNDEEP
mysqlsqldatabasegroup-by
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

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 customer. If customer has not placed any order return 00-00-0000 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?

Upvotes: 0

Views: 7799

Answers (2)

Avishek
Avishek

Reputation: 824

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

Upvotes: 5

Haresh Vidja
Haresh Vidja

Reputation: 8496

You can do as below

SELECT customer_table.*, orders.order_date FROM customer_table left join (SELECT customer_id, order_date FROM (SELECT tmp.* from  order_table ORDER BY order_date DESC ) as tmp GROUP BY tmp.customer_id) as orders on orders.customer_id= customer_table.id where order.order_date NOT NULL

Upvotes: 0

Related Questions