Aleks
asked on
Select record with the most recent date
Using the same query I had before I need to add another condition
The join to the 'passports' section sometimes returns more than one record. I need to only return the record where the 'c.validto' is the most recent.
The join to the 'passports' section sometimes returns more than one record. I need to only return the record where the 'c.validto' is the most recent.
--//* Export individuals to new table *//--
SELECT '1' AS Firmid ,
'contact' AS usertype ,
'1' AS FirmaddressIdcon ,
'0' AS ordernum ,
a.[Id] AS trackeruserid ,
'1' AS Imported ,
CAST(a.[FirstName] AS VARCHAR(30)) AS FirstNm ,
a.[MiddleName] AS MiddleNm ,
CAST(a.[LastName] AS VARCHAR(30)) AS LastNM ,
CAST(a.[Nickname] AS VARCHAR(255)) AS OtherNms ,
CAST(a.[Prefix] AS VARCHAR(80)) AS Title ,
CAST(a.[MaidenName] AS VARCHAR(60)) AS MaidenNm ,
CAST(a.[DateOfBirth] AS VARCHAR(25)) AS Dob ,
CAST(a.[DateOfBirth] AS TIME) AS DobD ,
CAST(a.[BirthCity] AS VARCHAR(40)) AS Pob ,
CAST(a.[BirthProvinceState] AS VARCHAR(40)) AS PobState ,
a.[DateOpened] AS dateopened ,
CAST(a.[Gender] AS VARCHAR(20)) AS Sex ,
a.[IsActive] ,
a.ModifiedOn AS lastupdate ,
g.CountryName AS POBCountry ,
a.[MaritalStatus_id],
-- passport information (duplicates)
c.DocumentNumber AS passportnumber,
c.ValidFrom,
c.ValidTo,
d.CountryName,
-- email address (duplicates)
e.EmailAddress,
-- addresses (multiple)
h.InCareOf ,
h.StreetNumber + ' ' + h.StreetName AS Street,
h.UnitNumber ,
f.UnitType,
h.AdditionalAddressLine ,
h.City ,
h.Province ,
b.CountryName,
h.PostalCode ,
h.State_id ,
h.Street1 ,
h.Street2 ,
j.Category ,
j.Title
FROM [Tracker].[dbo].[Individuals] AS a -- name, etc.
LEFT JOIN Countries AS g ON g.[Id] = a.[BirthCountry_id] -- country of birth
--- THIS IS WHERE I WANT TO ADD THE CONDITION TO ONLY SELECT ONE RESULT IF THERE IS MORE THAN ONE, AND SHOULD BE THE ONE WHERE c.validto IS THE MORE RECENT (THIS IS A DATE FIELD) --
LEFT JOIN Passports AS c ON c.Individual_id = a.Id -- passport information
LEFT JOIN countries AS d ON d.Id = c.Country_id -- passport country more than one passport ***
LEFT JOIN dbo.IndividualEmails AS e ON e.Individual_id = a.Id -- there is more than one email ***
LEFT JOIN IndividualAddresses AS z ON a.Id = z.Individual_id -- addresses tables with link to address
LEFT JOIN Addresses AS h ON h.Id = z.Address_id -- actual address
LEFT JOIN AddressTypes AS j ON j.Id = z.AddressType_id -- residential
LEFT JOIN Countries AS b ON b.id = h.Country_id AND b.CountryName = 'United States' -- address country
LEFT JOIN AddressUnitTypes AS f ON f.Id = h.UnitType_id -- unite type apartment
SELECT * FROM dbo.Individuals
ASKER
Seems to work, can you explain in short what that query does please? I want to be able to understand it and then later on use it for other fields if necessary.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what is the qw at the end of the join ?
ASKER
Also, if that is the join I can get rid of the second line here
they are both joining the passports table, so the second line should be removed correct ?
Althought if I do that then the select can't find those fields. I guess we need both joins ?
CROSS APPLY (SELECT TOP 1 * FROM Passports k WHERE k.Individual_id = a.Id ORDER BY k.validto DESC )qw -- passport select one with valid to more recent only
LEFT JOIN Passports AS c ON c.Individual_id = a.Id -- passport information
LEFT JOIN countries AS d ON d.Id = c.Country_id -- passport country more than one passport ***
they are both joining the passports table, so the second line should be removed correct ?
Althought if I do that then the select can't find those fields. I guess we need both joins ?
Yes remove both lines and use below. I think May / MAY NOT work but need to test.
OUTER APPLY (SELECT TOP 1 * FROM Passports k WHERE k.Individual_id = a.Id ORDER BY k.validto DESC ) qw
qw - is the alias name of the query. We have to provide the alias name, consider this as just the syntax thing. Its like when we encapsulate a query inside we have to provide it a name.
Any ways you can use the query i gave.
Hope it helps!
OUTER APPLY (SELECT TOP 1 * FROM Passports k WHERE k.Individual_id = a.Id ORDER BY k.validto DESC ) qw
qw - is the alias name of the query. We have to provide the alias name, consider this as just the syntax thing. Its like when we encapsulate a query inside we have to provide it a name.
Any ways you can use the query i gave.
Hope it helps!
ASKER
you entered cross apply in the first query, and here outer apply. What is the difference ?
Hi,
>>you entered cross apply in the first query, and here outer apply. What is the difference ?
Consider cross apply as INNER JOIN and outer apply as LEFT JOIN. It internally basically a CROSS JOIN, with a where clause. Both are one the best things in SQL Server.
Hope it helps!
>>you entered cross apply in the first query, and here outer apply. What is the difference ?
Consider cross apply as INNER JOIN and outer apply as LEFT JOIN. It internally basically a CROSS JOIN, with a where clause. Both are one the best things in SQL Server.
Hope it helps!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note - TOP and ORDER BY are always works the best. ! With the above approach you have to join on Multiple things. and it will 200% create performance problems for you. Never use that kind of approach.
APPLY with TOP 1 always works the best. It will get out of the query with just 1 records. The above approach will scan the entire table.
Hope it helps !
APPLY with TOP 1 always works the best. It will get out of the query with just 1 records. The above approach will scan the entire table.
Hope it helps !
Pawan, you're a funny guy and I hope you're not believing in what you're saying.
amucinobluedot, if you can, please test both and compare them. Is the best way to see it.
Btw, CROSS APPLY is more used to apply a function to many rows. Using it in the place of a LEFT or INNER JOIN it may work but doesn't mean that you're using it right.
Cheers
amucinobluedot, if you can, please test both and compare them. Is the best way to see it.
Btw, CROSS APPLY is more used to apply a function to many rows. Using it in the place of a LEFT or INNER JOIN it may work but doesn't mean that you're using it right.
Cheers
Vitor , you're also a very funny guy. I don't think you have ever worked with APPLYs. Anyways I am here to help the author. Dont have much time to discuss the concepts in detail.
Also why we are considering performance it was never asked. One example my case works faster is given below-
https://www.experts-exchange.com/questions/28999182/SQL-syntax-How-to-update-multiple-table-entries-with-top-1-entry-from-table-2.html?notificationFollowed=183421805&anchorAnswerId=41988028#a41988028
Also why we are considering performance it was never asked. One example my case works faster is given below-
https://www.experts-exchange.com/questions/28999182/SQL-syntax-How-to-update-multiple-table-entries-with-top-1-entry-from-table-2.html?notificationFollowed=183421805&anchorAnswerId=41988028#a41988028
ASKER
Thankfully this is not a who is funnier contest :) I think both work fine, the test database won't really show too much of a difference in performance, so you are both right. Thanks for the help.
ASKER
Thanks to both.
Please try this -
Open in new window
Hope it helps!