Referencing Outer Query Tables in Inner Query

WRONG:

SELECT * FROM Table t1 INNER JOIN  ( SELECT TOP 1 t1.PrimaryKeyId Table t2 ON where t2.CustomerId = t1.CustomerId ORDER BY CreatedDate DESC)

When you run the above query, you would get an error – “Multi-Part Identifier Could Not Be Found”.

CORRECT:

SELECT * FROM Table t1 where t1.PrimaryKeyId = (SELECT TOP 1 PrimaryKeyId FROM  Table t2 WHERE t2.CustomerId = t1.CustomerId ORDER BY CreatedDate DESC)

This sub query runs for each of the PrimaryKeyIds from the Outer Query and get the TOP 1 row from the set of rows with same CustomerIDs.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s