0

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.

MAZux
  • 911
  • 1
  • 15
  • 28
  • Which MySQL version? – jarlh Jul 26 '19 at 11:42
  • Sorry it's `10.3.16-MariaDB` – MAZux Jul 26 '19 at 11:43
  • Hardly seems worth mentioning, but anyway... see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jul 26 '19 at 11:45

2 Answers2

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

if mysql 8.0+ version and maria db from 10.2 allow row_number() which will solve your problem

select * from (SELECT C.customer_id, O.order_date, COUNT(O.order_id) AS orders_count,
 row_number() over(partition  by  C.customer_id order by O.order_date DESC) rn
FROM customers AS C 
LEFT JOIN orders AS O ON O.customer_id = C.customer_id
 group by  C.customer_id, O.order_date
) a where a.rn=1
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63