Click here to Skip to main content
14,971,661 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
order table has order_id, Order_daterime, customer_id, item_id,amount, quantity column
customer table has customer_id, name, gender, country, age column and item table has item_id and category.

What I have tried:

how to get second order id and count last 7 days.
Updated 19-May-21 8:33am
SeanChupas 19-May-21 13:56pm
Count(*) and a proper WHERE clause based on date will get you the number of order in last 7 days.

ROW_NUMBER with PARTITION - do not know what the equivalent is in MySql - will get you the second.
Rafa Nadal 19-May-21 14:10pm
not able to understand it can you help me with a example so i can understand it??

1 solution

Perhaps you could try modifying the query from something like this
WITH LatestOrders AS (
   SELECT o.*
   FROM       Customer c
   INNER JOIN Order    o ON o.Customer_Id = c.Customer_Id
               FROM   Order o2
               WHERE  o2.Customer_Id    =  c.Customer_Id
               AND    o2.Order_DateTime <= CURDATE() - 7)
   ORDER BY o.Order_DateTime
   LIMIT 2
FROM LatestOrders lo
ORDER BY lo.Order_DateTime DESC

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900