[SQL] Select 3 lastest order for each customer

Question

Hi

Here is my table CusOrder that collect 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 lastest order for each customer and I need all customer so it will show each customer and his/her 3 lastest order how can I do it

sorry for my bad english

Answer 1

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

Answer 2

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

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

Answer 3

"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 output you need but this should not be hard.