Referencing Outer Query Tables in Inner Query


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”.


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.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s