The task is to only retrieve the top record becuase there are many records that has the same
countryId and Name but among them the oldest should be picked only.
SELECT Name,CountryIdRetrieved
FROM (
SELECT TOP 1 Name
,(
SELECT Id
FROM country
WHERE TRIM(ISNULL(Name, '')) = (
SELECT TOP 1 TRIM(ISNULL(Name, '')) Name
FROM TestingDb.dbo.Country cs
WHERE cs.Id = [CountryId]
)
AND TRIM(ISNULL(ContinentName, '')) = (
SELECT TOP 1 TRIM(ISNULL(ContinentName, '')) ContinentName
FROM TestingDb.dbo.Country cs
WHERE cs.Id = [CountryId]
)
) CountryIdRetrieved
FROM TestingDb.dbo.CType cse
) AS q
WHERE NOT EXISTS (
SELECT 1
FROM CType ct
WHERE TRIM(ISNULL(Name, '')) = TRIM(ISNULL(ct.Name, ''))
AND CountryIdRetrieved = ct.CountryId
)
but it returns nothing if I put SELECT TOP 1 NAME in the first subquery
SELECT Name,CountryIdRetrieved
FROM (
SELECT TOP 1 Name
,(
but if i remove this TOP 1 then it returns correct results.
why?
