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.
Today I was working on a SQL query, and it took me a while before I figured out an issue.
A query goes something like this – SELECT * FROM Table t1 where t1.UserId NOT IN ( SELECT UserId FROM Table t2 WHERE t2.Status = 1) WHERE Status = 2
Lets assume Status 1 = Active and Status 2 = Terminated and UserId can be NULL. There are few records in Table with UserIds Null for Status = 1. When i ran the query I was expecting few records to output but for some reason there are 0 records. After looking at the query carefully, i noticed that the inner query was returning NULL values and that ends being as below. It return NULL
SELECT * FROM Table t1 where t1.UserId NOT IN ( 1, 2, NULL) — Return 0 records although there are matching records with 1, 2
NULL can be dangerous enough to mess up the whole query and result set. So better handle NULL values properly.
“Often the answer is baked into the question you ask, so, if you don’t question the questions you’re asking you’re not going to come up with really innovative solutions.”