Mega Code Archive
Join with (inline)Table Expressions
<![CDATA[
5> CREATE TABLE Orders (
6> OrderID int IDENTITY (1, 1) NOT NULL ,
7> CustomerID nchar (5) NULL ,
8> EmployeeID int NULL ,
9> OrderDate datetime NULL ,
10> RequiredDate datetime NULL ,
11> ShippedDate datetime NULL ,
12> ShipVia int NULL ,
13> Freight money NULL DEFAULT (0),
14> ShipName nvarchar (40) NULL ,
15> ShipAddress nvarchar (60) NULL ,
16> ShipCity nvarchar (15) NULL ,
17> ShipRegion nvarchar (15) NULL ,
18> ShipPostalCode nvarchar (10) NULL ,
19> ShipCountry nvarchar (15) NULL
20> )
21> GO
1>
2>
3>
4> SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDate
5> FROM dbo.Orders AS O
6> JOIN (SELECT EmployeeID, MAX(OrderID) AS MaxOid
7> FROM dbo.Orders
8> GROUP BY EmployeeID) AS D
9> ON O.OrderID = D.MaxOid;
10> GO
OrderID EmployeeID CustomerID OrderDate
----------- ----------- ---------- -----------------------
(0 rows affected)
1>
2> drop table Orders;
3> GO
1>
]]>