Get the latest order_date after aggregation my rows

I have 2 tables:

• Customers table (which has a customer_id column)
• Orders table (which has customer_id and order_date columns)

Is there a way to fetch all orders count for each customer with the latest order date in a single query ordered by the orders count, without using correlated sub-query?

I already have this:

SELECT C.customer_id, O.order_date, COUNT(O.order_id) AS orders_count 
FROM customers AS C
LEFT JOIN orders AS O ON O.customer_id = C.customer_id
GROUP BY C.customer_id
ORDER BY COUNT(O.order_id) DESC, O.order_date DESC 

However, I only get the date of the first order the customer made.

Answer

Use the MAX() aggregation function:

SELECT C.customer_id, MAX(O.order_date), COUNT(O.order_id) AS orders_count 
FROM customers C LEFT JOIN
     orders O
     ON O.customer_id = C.customer_id
GROUP BY C.customer_id
ORDER BY COUNT(O.order_id) DESC, MAX(O.order_date) DESC