DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to form correlated SELECT list item?

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

.....

*/

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE