SQL query to get three most recent records by customer

Question

I have a table of orders and am looking to get the three most recent orders by customer id

customer    orderID     orderDate     
1           234          2018-01-01    
1           236          2017-02-01       
3           256          20157-03-01      

I was able to use row number () to identify the row number of each line in the table, but is there a way to get the three most recent orders by customer id? Some customers do have less than 3 orders while others have more than 10 orders so I wasn't able to specify by the row number.

Does anyone have recommendations for a different option?

Answer 1

Here is an interesting approach using apply (and assuming you have a customers table):

select o.*
from customers c cross apply
     (select top 3 o.*
      from orders o
      where o.customerid = c.customerid
      order by orderdate desc
     ) o;

Answer 2

You could use partition by;

select customerid, orderid,orderdate from (
select t.customerid, t.orderid,t.orderdate
,row_number() over (partition by t.customerid order by t.orderDate desc) as mostRecently
from samplecustomers t)
Records where mostRecently < 4

Answer 3

Use this query:

SELECT result.customer
     , result.orderID
     , result.orderDate
 FROM
     (
     SELECT Temp.customer
          , Temp.orderID
          , Temp.orderDate
          , ROW_NUMBER() OVER(PARTITION BY Temp.customer 
                                  ORDER BY Temp.orderDate DESC) AS MR
      FROM YourTable AS Temp
     )  AS result
WHERE result.MR <= 3;

Answer 4

Try this:

SELECT * 
  FROM orders 
 WHERE customer = 1 
 ORDER BY orderDate ASC limit 3

This should solve the problem.

Answer 5

How about this:

select a.Customer, a.orderID, a.orderDate   
from orders a  
where a.orderID in  
(  
select top 3 b.orderID  
from orders b  
where b.Customer = a.Customer  
order by b.orderDate desc  
)  
order by a.Customer, a.orderID, a.orderDate