SQL Server 2008 - get last record from registered table
I have a SQL Server 2008 database. This database has two Customer and Order tables. 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 clients in my database. If the user has placed at least one order, I want to return information related to the last order placed. I currently 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 customer related orders. In fact, although I only want the very last. How do I do this in SQL?
Thanks!
+2
a source to share
5 answers
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
+1
a source to share
Here's a method that doesn't assume 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');
+6
a source to share
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
0
a source to share