TOP 1 WITH TIES

The new APPLY Operator lets you reference one or more columns from the LEFT table in the right Derived table or table valued function. There are to variations of the Operator, CROSS APPLY and OUTER APPLY. You can use CROSS APPLY like an INNER JOIN except that you dont have to specify any JOIN condition and you can reference the left table columns in the right hand side of the Operator. OUTER APPLY is like LEFT JOIN, that all rows from the left table is included in the result even when there is no match.

The main advantage of the CROSS APPLY operator is that it lets you pass values from the columns of the joined table to a table valued UDF, which was not possible in previous versions of SQL Server.

Suppose you have the following table structure.

CREATE TABLE #Client(ClientId int, ClientName VARCHAR(35))
CREATE TABLE #Account(AccountId int, ClientId int, AccountValue numeric(18,2))

INSERT INTO #Client VALUES(1, 'Anil')
INSERT INTO #Client VALUES(2, 'Robin')

INSERT INTO #Account VALUES(1,1,100)
INSERT INTO #Account VALUES(2,1,200)
INSERT INTO #Account VALUES(3,1,300)
INSERT INTO #Account VALUES(4,2,100)
INSERT INTO #Account VALUES(5,2,400)
INSERT INTO #Account VALUES(6,2,500)


And you were asked to get the TOP 2 Accounts based on AccountValue of each client.

Is SQL Server 2000 you can achieve it by writing a query like,

SELECT C.ClientName, A.AccountValue
FROM #Client C
INNER JOIN #Account A
ON C.ClientId=A.ClientId
AND A.AccountID IN(
SELECT TOP 2 AccountId
FROM #Account D
WHERE D.ClientID=A.ClientId
ORDER BY D.AccountValue DESC)


Which is ofcourse costly. See how CROSS APPLY makes life easy for you.

SELECT C.ClientName, A.AccountValue
FROM #Client C
CROSS APPLY
(SELECT TOP 2 AccountId, AccountValue
FROM #Account D
WHERE D.ClientID=C.ClientId
ORDER BY D.AccountValue DESC)A

Here is the result of the above two queries.

ClientName	AccountValue
Anil	300.00
Anil	200.00
Robin	500.00
Robin	400.00

To see OUTER APPLY at work, Lets add a client without any accounts and try the above query with OUTER APPLY

INSERT INTO #Client VALUES(3, 'Roji')

SELECT C.ClientName, A.AccountValue
FROM #Client C
OUTER APPLY
(SELECT TOP 2 AccountId, AccountValue
FROM #Account D
WHERE D.ClientID=C.ClientId
ORDER BY D.AccountValue DESC)A


And here is the result.

ClientName	AccountValue
Anil	300.00
Anil	200.00
Robin	500.00
Robin	400.00
Roji	NULL

You feel the real power of OUTER/CROSS APPLY When you use them with the Table Values UDFs. There is a UDF named dbo.ufnGetContactInformation in the new AdventureWorks Sample database, which accepts a ContactID as the input and return all contact details as a table. Lets try using that function to get the contact details of all the contacts in the Sales.ContactCreditCard table.


SELECT B.FirstName, B.LastName, B.JobTitle, B.ContactType
FROM Sales.ContactCreditCard A
CROSS APPLY dbo.ufnGetContactInformation(A.ContactId) B

And here is the (abridged) result.

FirstName	LastName	JobTitle	ContactType
Catherine	Abel	Owner	Store Contact
Kim	Abercrombie	Owner	Store Contact
Humberto	Acevedo	Owner	Store Contact
Pilar	Ackerman	Owner	Store Contact

But thats just an introduction. I had seen a lot of smart SQL using CROSS APPLY in NG posting recently. So stay tuned.