join just last record in second table, but include records don't have match in second table

Question

I know there are a lot similar questions. Actually I used this and it works, but I can't figure out how to include records, which don't have match in second table.

I use sample Northwind db on MS SQL Server.

Using this Query:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN
    (
        SELECT CustomerID, MAX(OrderDate) maxDate
        FROM Orders
        GROUP BY CustomerID
    ) b ON Orders.CustomerID = b.CustomerID AND
            Orders.OrderDate = b.maxDate
ORDER BY Orders.OrderDate

I get correct result, but missing records, which don't match.

If I use LEFT OUTER JOIN instead INNER JOIN:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
LEFT OUTER JOIN
    (
        SELECT CustomerID, MAX(OrderDate) maxDate
        FROM Orders
        GROUP BY CustomerID
    ) b ON Orders.CustomerID = b.CustomerID AND
            Orders.OrderDate = b.maxDate
ORDER BY Orders.OrderDate

I get missing records, but in this case I have repeated customer names.

I want: get list of customers with only his last order and if he doesn't have order his name should be present anyway.

Are you sure you don't get duplicate customer names with the inner join? — Gordon Linoff, Feb 06 '14 at 19:07
no, don't have, just run using ORDER BY Customers.CustomerID to check. — Alexan, Feb 06 '14 at 19:09
it looks I found solution here: http://stackoverflow.com/questions/11764413/join-record-with-most-recent-record-on-second-table?rq=1 — Alexan, Feb 06 '14 at 19:17
. . I don't understand why the left outer join would result in duplicate customer names. The `left outer join` is one solution, although window functions are a better solution. — Gordon Linoff, Feb 06 '14 at 19:31

score 3 · Accepted Answer · answered Feb 06 '14 at 20:24

You got repeated customer names, because you link over orderdate. So if you have two or more orders on the last date for some customer you get all these last orders. If I assume that the orderid has the same sequence as the orderdate, following statement should return only one line for each customer.

select cs.*, o.* from customers cs
left outer join (
  select customerid, max(orderid) as orderid from orders
  group by customerid
) lnk on cs.customerid = lnk.customerid
left outer join orders o on lnk.orderid = o.orderid
order by cs.customerid

You're right about OrderID, I did these changes in my query. — Alexan, Feb 07 '14 at 01:51

score 1 · Answer 2 · 2014-02-07T12:58:15.980

1

Best way to do this would be CTE with ROW_NUMBER() this query will have better cost because you hit Orders table only once instead of twice to get the data and once to get max record.

WITH    LastOrder
          AS ( SELECT CustomerID
                   ,OrderID
                   ,OrderDate
                   ,ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate DESC ) AS RowNum
                FROM Orders)
    SELECT c.CustomerID
           ,c.CompanyName
           ,lo.OrderID
           ,lo.OrderDate
        FROM Customers AS c
        LEFT OUTER JOIN LastOrder AS lo
            ON c.Customer_id = lo.CustomerID
               AND lo.RowNum = 1

edited Feb 07 '14 at 12:58

answered Feb 06 '14 at 20:10

no, it shows repeated customer names with different orders – Alexan Feb 06 '14 at 23:32
@Alex I had to fix my `PARTITION BY` clause. Needed to remove `OrderID` from it. now it should only show last order. – Feb 07 '14 at 12:59

score 0 · Answer 3 · edited May 23 '17 at 12:14

I used this answer to resolve this problem.

See the code:

select Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
 from Customers
left outer join  Orders ON Customers.CustomerID=Orders.CustomerID
where   Orders.OrderDate is null OR
       Orders.OrderDate = 
       ( SELECT MAX(OrderDate)
           FROM Orders
          WHERE Customers.CustomerID=Orders.CustomerID)       
ORDER BY Customers.CustomerID

It did exactly what I want.

UPDATE: It's better to use OrderID instead OrderTime:

select Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
 from Customers
left outer join  Orders ON Customers.CustomerID=Orders.CustomerID
where   Orders.OrderID is null OR
       Orders.OrderID = 
       ( SELECT MAX(OrderID)
           FROM Orders
          WHERE Customers.CustomerID=Orders.CustomerID)       
ORDER BY Customers.CustomerID

join just last record in second table, but include records don't have match in second table

3 Answers3