SQL Server 2008 - Get Latest Record from Joined Table

I have a SQL Server 2008 database. This database has two tables called Customer and Order. These tables are defined as follows:

Customer
--------
ID,
First Name,
Last Name

Order
-----
ID,
CustomerID,
Date,
Description

I am trying to write a query that returns all of the customers in my database. If the user has placed at least one order, I want to return the information associated with the most recent order placed. Currently, I have the following:

SELECT
  *
FROM
  Customer c LEFT OUTER JOIN Order o ON c.[ID]=o.[CustomerID]

As you can imagine, this will return all of the orders associated with a customer. In reality though, I only want the开发者_开发知识库 most recent one. How do I do this in SQL?

Thank you!

Here's a method that doesn't assume that the order dates are unique:

SELECT
    Customer.ID CustomerID,
    Customer.FirstName,
    Customer.LastName,
    T1.ID OrderID,
    T1.Date OrderDate,
    T1.Description OrderDescription
FROM Customer
LEFT JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Date DESC) AS rn
    FROM [Order]
) T1
ON Customer.ID = T1.CustomerID AND T1.rn = 1

Result:

CustomerID  FirstName   LastName   OrderID  OrderDate   OrderDescription  
1           FirstName1  LastName1  2        2010-05-02  Description2      
2           FirstName2  LastName2  3        2010-05-03  Description3      
3           FirstName3  LastName3  NULL     NULL        NULL              

Test data:

CREATE TABLE Customer (ID INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100) NOT NULL);
INSERT INTO Customer (ID, FirstName, LastName) VALUES
(1, 'FirstName1', 'LastName1'),
(2, 'FirstName2', 'LastName2'),
(3, 'FirstName3', 'LastName3');

CREATE TABLE [Order] (ID INT NOT NULL, CustomerID INT NOT NULL, Date DATE NOT NULL, Description NVARCHAR(100) NOT NULL);
INSERT INTO [Order] (ID, CustomerID, Date, Description) VALUES
(1, 1, '2010-05-01', 'Description1'),
(2, 1, '2010-05-02', 'Description2'),
(3, 2, '2010-05-03', 'Description3'),
(4, 2, '2010-05-03', 'Description4');

select c.ID, c.FirstName, c.LastName, o.ID as OrderID, o.Date, o.Description
from Customer c 
left outer join (
    select CustomerID, max(Date) as MaxDate
    from Order
    group by CustomerID
) om on c.ID = om.CustomerID 
left outer join Order o on om.CustomerID = o.CustomerID and om.MaxDate = o.Date

I would use where clause with Max() function to guarantee the latest added record:

(you code...) Where o.id = max(o.id)

Select * from 
Customer C Left join 
(
    Select o.CustomerID, Description, Date from 
    Orders o inner join
    (
        Select CustomerID, Max(Date) as LastOrder
        From Orders Group by CustomerID
    ) SubLatest on o.CustomerID = SubLatest.CustomerID
    and o.Date = SubLatest.LastOrder 
    ) SubDetails 
on C.id = SubDetails.CustomerID

Not Homework I hope! :O)

Select  Top 1  C.*
              ,O.*
From    Customer C left outer join
        Order O on O.CustomerId = C.Id
Order by O.[Date] Desc

Hope that helps