CODEWITHSUNDEEP
mysql
Maulik
Maulik

Reputation: 97

Fetch last three orders from a table

Consider a table with the following columns:

  1. Customer Email ID
  2. Payment Method (COD/ Netbanking/ CreditCard/ DebitCard)
  3. Order ID
  4. Order Creation Date
  5. Order Status(Success/ Failed/ Cancelled)

How do I fetch the last three successful orders past 3 months for each customer from these table in SQL along with the relevant details?

Upvotes: 0

Views: 121

Answers (2)

Faizan Younus
Faizan Younus

Reputation: 803

I Hope this helps 

SELECT *
FROM TABLENAME
WHERE OrderStatus='Success'
ORDER BY OrderCreationDate DESC
LIMIT 3;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269983

This is a bit painful in MySQL. Probably the simplest method is to use variables.

Your column names are not clear. And you have some additional conditions, but this is the basic idea:

select t.*
from (select t.*,
             (@rn := if(@c = customerid, @rn + 1,
                        if(@c := customerid, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @rn := 0, @c := '') params
      order by customerid, orderdate desc
     ) t
where rn <= 3;

You can add the additional where conditions to the subquery.

Upvotes: 1

Related Questions