Have Geek Brain

Select the last 3 orders for each customer

Here is my CusOrder table that collects the customer order

OrderID       Cus_ID    Product_ID  NumberOrder      OrderDate
   1        0000000001      9          1        6/5/2553 0:00:00
   2        0000000001     10          1        6/5/2553 0:00:00
   3        0000000004      9          2        13/4/2553 0:00:00
   4        0000000004      9           1        17/3/2553 0:00:00
   5        0000000002      9           1        22/1/2553 0:00:00
   7        0000000005      9           1        16/12/2552 0:00:00
   8        0000000003      9           3        13/12/2552 0:00:00
  10        0000000001      9           2        19/11/2552 0:00:00
  11        0000000003      9           2        10/11/2552 0:00:00
  12        0000000002      9           1        23/11/2552 0:00:00

I need to select 3 last orders for each customer and I need all customers so it will show each customer and his / her 3 last orders how can I do this

sorry for my bad english

+2


a source to share


3 answers 


WITH Temp AS
(
    SELECT *, (ROW_NUMBER() OVER (PARTITION BY Cus_ID ORDER BY OrderDate DESC)) AS Number
    FROM CusOrder
)
SELECT * FROM Temp WHERE Number <= 3


Must work. Not tested with this exact database structure, but something similar.

0


a source


This CTE should work for you:



;with cteTop3PerGroup as
(
   select row_number() over(partition by Cus_ID order by OrderDate DESC) as RN
          , *
   from CusOrder 
)
select * from cteTop3PerGroup
where RN <= 3
+2


a source


A "pure" SQL solution.


With Customers As (
    Select Cus_ID From test Group By Cus_ID
    ),
TopLastOrders as (
    Select o.Cus_ID, Max(OrderDate) as OrderDate
    From test o Inner Join Customers c on c.Cus_ID = o.Cus_ID
    Group By o.Cus_ID
    ),
TopSecondOrders as (
    Select o.Cus_ID, Max(OrderDate) as OrderDate
    From test o Inner Join Customers c on c.Cus_ID = o.Cus_ID
    Where Not Exists (Select 1 From TopLastOrders Where Cus_ID=o.Cus_ID And OrderDate=o.OrderDate)
    Group By o.Cus_ID
    ),
TopThirdOrders as (
    Select o.Cus_ID, Max(OrderDate) as OrderDate
    From test o Inner Join Customers c on c.Cus_ID = o.Cus_ID
    Where Not Exists (Select 1 From TopLastOrders Where Cus_ID=o.Cus_ID And OrderDate=o.OrderDate)
        And Not Exists (Select 1 From TopSecondOrders Where Cus_ID=o.Cus_ID And OrderDate=o.OrderDate)
    Group By o.Cus_ID
    )

Select c.Cus_ID, t1.OrderDate, t2.OrderDate, t3.OrderDate From Customers c
Left Outer Join TopLastOrders t1 on t1.Cus_ID = c.Cus_ID Left Outer Join TopLastOrders t2 on t2.Cus_ID = c.Cus_ID Left Outer Join TopLastOrders t3 on t3.Cus_ID = c.Cus_ID



Order By c.Cus_ID



I'm not sure what kind of result you want, but it shouldn't be difficult.

0


a source






More articles:


All Articles