I am writing an SQL query to get data from more than 3 tables, but for simplifying the question here I am using a similar scenario with 3 tables.

Table1 Customer (PK-CustomerID, Name)

Table2 Sales (FK-Id, SalePrice)

Table3 Order (PK-Id, FK-CustomerID, Date, Amount)

In the output, Date and Amount should be the from most recent Order (latest Date), for a customer

My approach was

    Select c.CustomerID, c.Name, s.SalePrice, RecentOrder.Date, RecentOrder.Amount from 
    Customer as c
    LEFT JOIN Sales s ON c.CustomerID = s.ID
    LEFT JOIN (SELECT top 1 o.Date, o.Amount, o.CustomerID  
    FROM Order o, Customer c1 WHERE c1.CustomerID = o.CustomerID ORDER BY o.Date DESC) 
    RecentOrder ON c.CustomerID = RecentOrder.CustomerID

Output I get

CustomerID, Name, SalePrice, Date, Amount

The output I get includes the most recent order out of all the orders. But I want to get the recent order out of the orders made by that customer

Output Required

CustomerID, Name, SalePrice, Date, Amount

CodePudding user response：

You need to pre-aggregate or identify the most recent order for each customer order, your query is selecting 1 row for all orders.

Try the following (untested!)

select c.CustomerID, c.Name, s.SalePrice, o.Date, o.Amount 
from Customer c
left join Sales s on c.CustomerID = s.ID
outer apply (
    select top (1) date, amount
    from [order] o
    where o.CustomerId=c.CustomerId
    order by Id desc
)o

CodePudding user response：

Instead of subquery in left join, you can check with outer apply.

Check following way

Select c.CustomerID, c.Name, s.SalePrice, RecentOrder.Date, RecentOrder.Amount 
from Customer c
LEFT JOIN Sales s ON c.CustomerID = s.ID
OUTER APPLY (
SELECT top 1 o.Date, o.Amount, o.CustomerID  
FROM [Order] o 
WHERE o.CustomerID = c.CustomerID ORDER BY o.Date DESC) RecentOrder`