title: Snowflake fetch latest record from identical field
body: <p>I want to insert records with distinct DIGITALID which has the lastest SRC_CREATED. That is, if there are 4 identical digitalis but rest other fields are different I want only the latest records ie record with MAX(SRC_CREATED). How can the query be modified to fetch only the latest record In case of identical digitalid?</p>
  <p>Scenario mentioned :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">DIGITALID</th>
  <th style="text-align: center;">DATA_TYPE</th>
  <th style="text-align: right;">SRC_CREATED</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">click</td>
  <td style="text-align: right;">2021-04-10 01:50:08.490</td>
  </tr>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">click</td>
  <td style="text-align: right;">2021-03-10 02:221:05.115</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">open</td>
  <td style="text-align: right;">2021-06-10 01:12:08.288</td>
  </tr>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">click</td>
  <td style="text-align: right;">2021-08-10 01:50:08.490</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Outcome required :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">DIGITALID</th>
  <th style="text-align: center;">DATA_TYPE</th>
  <th style="text-align: right;">SRC_CREATED</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">click</td>
  <td style="text-align: right;">2021-08-10 01:50:08.490</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">open</td>
  <td style="text-align: right;">2021-06-10 01:12:08.288</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Query to be edited :</p>
  <pre><code>INSERT INTO &quot;${intgdatabase}&quot;.&quot;${profileschema}&quot;.CUSTOMER_PROFILE_DETAILS
  (
      ADDRESS ,
      AGE ,
      CHURN_RATE ,
      CITY ,
      CITY_HASHED ,
      CONVERSION_TIME ,
      COUNTRY ,
      COUNTRY_CODE_HASHED ,
      DATE_OF_BIRTH_DAY_HASHED ,
      DATE_OF_BIRTH_MONTH_HASHED ,
      DATE_OF_BIRTH_YEAR_HASHED ,
      EMAIL ,
      EMAIL_ADDRESS_HASHED ,
      FIRST_NAME ,
      FIRST_NAME_HASHED ,
      FIRST_NAME_INITIAL_HASHED ,
      GENDER ,
      GENDER_HASHED ,
      HUX_ID ,
      IDENTITY_RESOLUTION ,
      LAST_CLICK ,
      LAST_EMAIL_OPEN ,
      LAST_NAME ,
      LAST_NAME_HASHED ,
      LTV_PREDICTED ,
      MATCH_CONFIDENCE ,
      MOBILE_DEVICE_ID ,
      PHONE ,
      PHONE_NUMBER_DIGITS_ONLY_HASHED ,
      POSTAL_CODE_HASHED ,
      PREFERENCE_EMAIL ,
      PREFERENCE_PUSH ,
      PREFERENCE_SMS ,
      PREFERENCE_IN_APP ,
      PROPENSITY_TO_PURCHASE ,
      PROPENSITY_TO_UNSUBSCRIBE ,
      SINCE ,
      STATE ,
      STATE_OR_PROVINCE_HASHED ,
      ZIP
  )
      SELECT CONCAT(NVL(ADD1,''),NVL(ADD2,'')) AS ADDRESS,
      NULL AS AGE,
      NULL AS CHURN_RATE,
      NVL(CITY,'') AS CITY,
      SHA2(NVL(CITY,'')) AS CITY_HASHED,
      NULL AS CONVERSION_TIME,
      'USA' AS COUNTRY,
      SHA2(NVL(CNTRYCODE,'')) AS COUNTRY_CODE_HASHED,
      NULL AS DATE_OF_BIRTH_DAY_HASHED,
      NULL AS DATE_OF_BIRTH_MONTH_HASHED,
      NULL AS DATE_OF_BIRTH_YEAR_HASHED,
      NVL(IDT.EMAIL,'') AS EMAIL,
      SHA2(NVL(IDT.EMAIL,'')) AS EMAIL_ADDRESS_HASHED,
      NVL(IDT.FNAME,'') AS FIRST_NAME,
      SHA2(NVL(IDT.FNAME,'')) AS FIRST_NAME_HASHED,
      NULL AS FIRST_NAME_INITIAL_HASHED,
      CASE
      WHEN NVL(GENDER,'')='F' THEN 'Female'
      WHEN NVL(GENDER,'')='M' THEN 'Male'
      WHEN NVL(GENDER,'')='U' Then 'Other'
      ELSE ''
      END AS GENDER,
      SHA2(NVL(GENDER,'')) AS GENDER_HASHED,
      IDT.DIGITALID AS HUX_ID,
      NULL AS IDENTITY_RESOLUTION,
      C.CLICK AS LAST_CLICK,
      C.OPENS AS LAST_EMAIL_OPEN,
      NVL(IDT.LNAME,'') AS LAST_NAME,
      SHA2(NVL(IDT.LNAME,'')) AS LAST_NAME_HASHED,
      NULL AS LTV_PREDICTED,
      NULL AS MATCH_CONFIDENCE,
      NULL AS MOBILE_DEVICE_ID,
      EI.PHONE AS PHONE,
      SHA2(EI.PHONE) AS PHONE_NUMBER_DIGITS_ONLY_HASHED,
      SHA2(NVL(ZIP,'')) AS POSTAL_CODE_HASHED,
      NULL AS PREFERENCE_EMAIL,
      NULL AS PREFERENCE_IN_APP,
      NULL AS PREFERENCE_PUSH,
      NULL AS PREFERENCE_SMS,
      NULL AS PROPENSITY_TO_PURCHASE,
      NULL AS PROPENSITY_TO_UNSUBSCRIBE,
      IDT.SRC_CREATED AS SINCE,
      NVL(STATE,'') AS STATE,
      SHA2(NVL(STATE,'')) AS STATE_OR_PROVINCE_HASHED,
      NVL(ZIP,'') AS ZIP
  FROM &quot;${basedatabase}&quot;.&quot;${baseschema}&quot;.&quot;IDENTITY&quot; IDT
  LEFT JOIN &quot;${basedatabase}&quot;.&quot;${baseschema}&quot;.&quot;EMAIL_INTERACTIONS&quot; EI
  ON IDT.DIGITALID = EI.DIGITALID
  LEFT JOIN (
      SELECT DIGITALID
        , MAX(CASE WHEN EVENT_TYPE = 'click' THEN SRC_CREATED ELSE NULL END) CLICK
        , MAX(CASE WHEN EVENT_TYPE = 'open' THEN SRC_CREATED ELSE NULL END) OPENS
      FROM &quot;${basedatabase}&quot;.&quot;${baseschema}&quot;.&quot;EMAIL_INTERACTIONS&quot;
      GROUP BY DIGITALID
  ) C ON IDT.DIGITALID = C.DIGITALID;
  </code></pre>