CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

How to fetch all orders excluding very first order of each customer in mysql

Here's my orders table:

enter image description here

I want to select all orders excluding very first order of each customer (if customer has placed multiple orders).

So if a customer e.g. 215 has total 8 orders, then I will select his all last 7 orders excluding his very first order 70000 which was placed on 10 July 2017.

But if a customer e.g. 219 had placed only one order 70007, it must be selected by the query.

Upvotes: 0

Views: 403

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Using an anti-join approach:

SELECT o1.order_id, o1.customer_id, o1.order_date, o1.order_value
FROM orders o1
LEFT JOIN
(
    SELECT customer_id, MIN(order_date) AS min_order_date, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
) o2
    ON o1.customer_id = o2.customer_id AND
       o1.order_date = o2.min_order_date
WHERE
    o2.customer_site = 1 AND
    (o2.customer_id IS NULL OR
    o2.cnt = 1);

The idea here is to try to match each record in orders to a record in the subquery, which contains only first order records, for each customer. If we can't find a match, then such an order record cannot be the first.

Upvotes: 1

Sayem
Sayem

Reputation: 104

Solution

Dear @Tim Biegeleisen, your answer almost done. just add HAVING COUNT(customer_id)>1 So the query is below:

SELECT o1.order_id, o1.customer_id, o1.order_date, o1.order_value
FROM orders o1 
LEFT JOIN (
  SELECT customer_id, MIN(order_date) AS min_order_date
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(customer_id)>1
) o2
ON o1.customer_id = o2.customer_id AND
   o1.order_date = o2.min_order_date
WHERE
o2.customer_id IS NULL;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below -

select order_id,customer_id,order_date,order_Value
from tablename
group by order_id,customer_id,order_date,order_Value
having count(order_id)=1
union all
select order_id,customer_id,order_date,order_Value
from tablename a where order_date not in (select min(order_date) from tablename b
where a.customer_id=b.customer_id)

Upvotes: 0

Related Questions