Rewrite OUTER APPLY to Redshift with subquery

Question

I rewriting sql server scripts to redshift database queries

I have OUTER APPLY construction

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 = <Parameters.DemographicsOrgId>
                   AND ca.DeletedDate IS NULL
                   AND ca.TypeId = 7
                   AND ca.ContactId = cc.Id
           ) q
   WHERE   q.rn = 1
   ) ca

But Redshift don't has outer apply. How Ican correctly rewrite it with LEFT JOIN?

UPDATE

I think about rewrite it like this

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 = <Parameters.DemographicsOrgId>
                   AND ca.DeletedDate IS NULL
                   AND ca.TypeId = 7
                   AND ca.ContactId = cc.Id
           ) q
   GROUP BY q.rn
   ) ca
ON ca.rn = 1

But is this correctly?

Answer 1

No, it does not look right. I would guess:

LEFT JOIN
(SELECT ca.OrganizationId,
        ROW_NUMBER() OVER (ORDER BY ca.Id DESC) AS rn,
        ca.StateProvince,
        ca.ZipPostalCode,
        ca.ContactId
 FROM  public.contact_addresses ca 
 WHERE ca.DeletedDate IS NULL AND
       ca.TypeId = 7
 GROUP BY ca.OrganizationId, ca.ContactId
) ca
ON ca.ContactId = cc.ID AND
   ca.OrganizationId = <Parameters.DemographicsOrgId> AND
   ca.rn = 1

Basically, you need to aggregate by the correlation conditions (if they are equality) and then use them for the outer ON conditions.