CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

How to fetch oldest and newest order for each customer in mysql?

This is my orders table:

enter image description here

I want to fetch oldest and newest order for each customer. Here you can see customer 231, 232, etc. have multiple orders. How can we get very first and latest order based on order_date for each customer?

So, here I would expect 2 MySQL queries to get required results.

Upvotes: 1

Views: 98

Answers (1)

forpas
forpas

Reputation: 164099

You can group by customer_id to get the min and max dates and then join to the table:

select o.* 
from orders o inner join (
  select customer_id, min(order_date) mindate, max(order_date) maxdate
  from orders
  group by customer_id
) g on o.customer_id = g.customer_id and (o.order_date in (g.mindate, g.maxdate))

Upvotes: 1

Related Questions