How to de-duplicate SQL query using ROW_NUMBER

Question

I try to de-deduplicate my SQL Query but it gives this error:

An error occurred while checking the query syntax. Errors: An error occurred while checking the query syntax. Errors: Invalid column name 'Actual_End_Date__c'.

The field actually IS a valid column name. The error refers to this line:

x.Actual_End_Date__c AS ActualEndDate

My Query:

SELECT 

x.id as Id
, x.Id as ContactId
, x.firstname as FirstName
, x.lastname as LastName
, x.Email as Email
, x.Actual_End_Date__c AS ActualEndDate


FROM 

(SELECT 
placement.id AS Id
, contact.Id AS ContactId
, contact.firstname AS FirstName
, contact.lastname AS LastName
, contact.Email AS Email
, placement.Actual_End_Date__c AS ActualEndDate
, ROW_NUMBER()OVER(PARTITION BY placement.candidate__c ORDER BY placement.Actual_End_Date__c DESC) AS rank 
FROM [Placement__c_Salesforce] placement 
        LEFT JOIN [contact_salesforce] contact ON contact.id = placement.candidate__c
        LEFT JOIN [Privacy_Settings__c_Salesforce] optin ON optin.contact__c = contact.id

WHERE placement.status__c = 'completed'        
AND contact.special_status__c is NULL
AND optin.Opt_in_for_Survey_emails__c = 'True'
AND datediff(m,placement.Actual_End_Date__c,GetUTCDate()) > -11) x


WHERE x.rank = 1

Answer 1

I think you were referencing the inner column names incorrectly in the outer select. Columns in the outer query don't need to be re-aliased -- they'll be what aliases you've assigned inside.

SELECT 
  x.id
, x.ContactId
, x.firstname
, x.lastname
, x.Email
, x.ActualEndDate
FROM 
(
    SELECT 
      placement.id
    , contact.Id AS ContactId
    , contact.firstname 
    , contact.lastname
    , contact.Email 
    , placement.Actual_End_Date__c AS ActualEndDate
    , ROW_NUMBER() OVER (PARTITION BY placement.candidate__c ORDER BY placement.Actual_End_Date__c DESC) AS rank 
    FROM [Placement__c_Salesforce] placement 
    LEFT JOIN [contact_salesforce] contact ON contact.id = placement.candidate__c
    LEFT JOIN [Privacy_Settings__c_Salesforce] optin ON optin.contact__c = contact.id
    WHERE placement.status__c = 'completed'        
    AND contact.special_status__c is NULL
    AND optin.Opt_in_for_Survey_emails__c = 'True'
    AND datediff(m,placement.Actual_End_Date__c,GetUTCDate()) > -11
) x
WHERE x.rank = 1