title: Retrieve the most recent person record added to an account
body: <p>I am trying to build a table of Contact IDs (Primary Keys) of the most recently created records assigned to each Account of a certain type in our Salesforce org.</p>
  
  <p>Working in Salesforce Marketing Cloud, I'm trying to build a sample list that I can setup to update automatically so the records I'm testing against are never stale. I only need one example from each account to do my testing. Since I want to make sure the record isn't stale, I want to select the most recent record assigned to each Account.</p>
  
  <p>Every Contact is assigned to one and only one Account. The Account ID lives as a foreign key on the Contact Record. The created date of the Contact is also a field on the Contact record.</p>
  
  <p>The Sample list needs to contain the email address, ContactID, and the name of the Management Company, which lives on the Account record.</p>
  
  <p>I figured doing a directional JOIN toward the Account table would do the trick, but that didn't work. I figure that's because there's nothing distinguishing which record to pick.</p>
  
  <p>This is what I've got for code, which is pretty useless...</p>
  
  <pre><code>SELECT
      C.Email AS Email,
      C.Id AS Id18,
      C.AccountID AS AccountID,
      A.Management_Company AS ManagementCompany
  FROM
      ENT.Contact_Salesforce_DE AS C 
      RIGHT JOIN ENT.Account_Salesforce_DE AS A ON C.AccountID = A.Id
  WHERE
      A.RecordTypeId = '1234567890ABCDEFGH' AND
      A.Management_Company IS NOT NULL AND
      C.Email IS NOT NULL
  </code></pre>
  
  <p>The syntax checks out, but I get a system error every time I run it.</p>
  
  <p>Marketing Cloud runs on an older version of SQL Server, so some more recent query functions won't always work.</p>
  
  <p>And yes, I'm a relative noob to SQL. Won't surprise me if this has a really simple solution, but I couldn't find another entry describing the solution, so...</p>