title: Rewrite OUTER APPLY to Redshift with subquery
body: <p>I rewriting sql server scripts to redshift database queries</p>
  
  <p>I have OUTER APPLY construction</p>
  
  <pre><code>OUTER APPLY
     (
     SELECT  q.*
     FROM    (
             SELECT  ROW_NUMBER() OVER(ORDER BY ca.Id DESC) AS rn,
                     ca.StateProvince,
                     ca.ZipPostalCode,
                     ca.ContactId
             FROM    public.contact_addresses ca 
             WHERE   ca.OrganizationId = &lt;Parameters.DemographicsOrgId&gt;
                     AND ca.DeletedDate IS NULL
                     AND ca.TypeId = 7
                     AND ca.ContactId = cc.Id
             ) q
     WHERE   q.rn = 1
     ) ca
  </code></pre>
  
  <p>But Redshift don't has outer apply. How Ican correctly rewrite it with LEFT JOIN?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I think about rewrite it like this</p>
  
  <pre><code>LEFT JOIN
     (
     SELECT  q.*,
             q.rn = 1
     FROM    (
             SELECT  ROW_NUMBER() OVER(ORDER BY ca.Id DESC) AS rn,
                     ca.StateProvince,
                     ca.ZipPostalCode,
                     ca.ContactId
             FROM    public.contact_addresses ca
             WHERE   ca.OrganizationId = &lt;Parameters.DemographicsOrgId&gt;
                     AND ca.DeletedDate IS NULL
                     AND ca.TypeId = 7
                     AND ca.ContactId = cc.Id
             ) q
     GROUP BY q.rn
     ) ca
  ON ca.rn = 1
  </code></pre>
  
  <p>But is this correctly?</p>