CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Itay.B
Itay.B

Reputation: 4111

Sql return last order details for each customer

Using SQL Server 2012.

I have a table T_Orders with few columns with the order details. In the table there are also columns ClientID -int, OrderDateTime - smalldatetime.

How can I return the last order for each client?

The problem is that if I do something like this : 

select max(OrderDateTime), ClientID 
from t_Orders 
group by ClientID

I will not get the rest of the information about the order because I didn't select all the columns. If I select all the columns, I will have to group by all of them and the result will not be the last order for this client.

Upvotes: 0

Views: 394

Answers (1)

user330315
user330315

Reputation:

This is usually solved using window functions:

select *
from (
  select o.*, 
         row_number() over (partition by o.ClientID order by o.OrderDateTime desc) as rn
  from t_orders o
) t
where rn = 1
order by ClientId;

Upvotes: 2

Related Questions