Join with (inline)Table Expressions : Inline view « Subquery « SQL Server / T-SQL Tutorial

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>

8.3.Inline view
	8.3.1.	Creating a Derived Table
	8.3.2.	Subqueries and Derived Tables
	8.3.3.	SELECT FROM (query that returns a regular result set) AS JOIN
	8.3.4.	A query that required a derived table.
	8.3.5.	In-Line Views (Derived Tables)
	8.3.6.	A query that uses a correlated subquery in its SELECT clause to retrieve the most recent Billing for each Banker
	8.3.7.	Queries in the FROM Clause
	8.3.8.	Select from nested select statements
	8.3.9.	Join with (inline)Table Expressions
	8.3.10.	Finding First Employee Using a Derived Table