The following Microsoft SQL Server T-SQL sample codes demonstrate the usage of correlated subquery as SELECT list expressions with column aliases:

USE Northwind

SELECT   A.CustomerID,

         MIN(A.OrderDate) AS FirstOrder,

         MAX(A.OrderDate) AS LastOrder,

         (SELECT   TOP 1 B.Freight

          FROM     Orders B

          WHERE    B.CustomerID = A.CustomerID

          ORDER BY OrderDate DESC) Freight

FROM     Orders A

GROUP BY A.CustomerID

ORDER BY A.CustomerID

/*

CustomerID  FirstOrder              LastOrder               Freight

ALFKI       1997-08-25 00:00:00.000 1998-04-09 00:00:00.000 1.21

ANATR       1996-09-18 00:00:00.000 1998-03-04 00:00:00.000 39.92

ANTON       1996-11-27 00:00:00.000 1998-01-28 00:00:00.000 58.43

AROUT       1996-11-15 00:00:00.000 1998-04-10 00:00:00.000 33.80

BERGS       1996-08-12 00:00:00.000 1998-03-04 00:00:00.000 151.52

BLAUS       1997-04-09 00:00:00.000 1998-04-29 00:00:00.000 31.14

BLONP       1996-07-25 00:00:00.000 1998-01-12 00:00:00.000 7.09

....

*/

------------

USE AdventureWorks;

GO

SELECT          c.LastName,

                c.FirstName,

                e.EmployeeID,

                (SELECT Bonus

                 FROM   Sales.SalesPerson sp

                 WHERE  e.EmployeeID = sp.SalesPersonID) AS Bonus

FROM   Person.Contact AS c

       INNER JOIN HumanResources.Employee AS e

         ON e.ContactID = c.ContactID

GO

/*

LastName    FirstName   EmployeeID  Bonus

.....

Campbell    David       283   3500.00

Alberts     Amy         284   0.00

Pak         Jae         285   5150.00

Varkey Chudukatil       286   985.00

Mensa-Annan Tete        287   3900.00

Abbas       Syed        288   0.00

Valdez      Rachel      289   75.00

.....

*/

------------