I want to merge these two tables and want distinct email based on latest execution date(ExecDate)

WITH cte_1 AS (
        
    SELECT
        Requester_Emails,
        Region,
        ExecDate 
    FROM
        monthly_tickets 
    WHERE
        Region LIKE 'new%' 
        AND
        Region IS NOT NULL
    
    UNION
        
    SELECT
        Requester_Emails,
        Region,
        ExecDate 
    FROM
        weekly_tickets 
    WHERE
        Region LIKE 'new%' 
        AND
        Region IS NOT NULL
),
cte_2 AS (
    SELECT
        *
    FROM
        cte_1
    ORDER BY
        ExecDate
)
SELECT
    DISTINCT( Requester_Emails ),
    Region
FROM
    cte_2

Sample input and output data:

CodePudding user response：

• You need to do an INNER JOIN back onto the initial results.
• DBFiddle:

  CodePudding user response：

  With Cte as 
  (
      Select Requester_Emails,Region ,ROW_NUMBER() OVER(PARTITION BY Requester_Emails ORDER BY ExecDate) Rn
      from monthly_tickets 
      where  Region  like 'new%' 
     -- and Region IS NOT NULL
  union
      Select Requester_Emails,Region ,ROW_NUMBER() OVER(PARTITION BY Requester_Emails ORDER BY ExecDate) Rn
      from weekly_tickets 
      where Region  like 'new%' 
      ---and Region IS NOT NULL 
  ),
  
  SELECT Requester_Emails,Region FROM cte 
  WHERE Rn=1