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.
Posted
Updated 19-May-21 8:33am
Comments
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
SQL
WITH LatestOrders AS (
   SELECT o.*
   FROM       Customer c
   INNER JOIN Order    o ON o.Customer_Id = c.Customer_Id
   WHERE 10 < (SELECT COUNT(*)
               FROM   Order o2
               WHERE  o2.Customer_Id    =  c.Customer_Id
               AND    o2.Order_DateTime <= CURDATE() - 7)
   ORDER BY o.Order_DateTime
   LIMIT 2
)
SELECT *
FROM LatestOrders lo
ORDER BY lo.Order_DateTime DESC
LIMIT 1
   

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