title: SQL partitions and use of analytical functions
body: <p>I have been trying to create a table with 2 columns party_id and matched_party_id for identifying duplicates as explained below:</p>
  
  <p>As you will see looking at the [First_NM+Last_NM+Zip_CD] we can identify few duplicate records. I want to match this and keep them within the umbrella of the most occurring party_id</p>
  
  <pre><code>PARTY_ID    FIRST_NM    LAST_NM ZIP_CD
  ----------------------------------------    
  95678   JANE    DOE 7075
  12345   JOHN    DOE 7000
  10000   JOHN    DOE 7075
  10000   JOHN    DOE 7075
  95678   JOHN    DOE 7075
  95678   JOHN    DOE 7075
  95678   JOHN    DOE 7075
  88648   JOHN    DOE 7075
  88648   JOHN    DOE 7075
  23456   JOHN    DOE 7075
  95678   SAM DOE 7075
  95678   SAM DOE 7075
  </code></pre>
  
  <p>Required Output</p>
  
  <pre><code>Party_ID    Matched_ID
  -----------------------
  95678   10000
  95678   88648
  95678   23456
  </code></pre>
  
  <p>since within a partition of duplicates we have identified 4 distinct party_id's
  namely 95678,10000,88648 and 23456 and 95678 is occurring the most number of times so need to match all other party_id's with that party_id.</p>
  
  <p>This is the code i am using. But failing for LEAD_PI null values</p>
  
  <pre><code>SELECT MAX_PI AS PARTY_ID, LEAD_PI AS MATCHED_ID from  
  (SELECT DISTINCT B.FIRST_NM, B.MDDL_NM, B.LAST_NM, B.ZIP_CD,MAX_PI,LEAD_PI,  
  FROM (SELECT I.PARTY_ID,I.FIRST_NM, I.MDDL_NM, I.LAST_NM,A.ZIP_CD,  
               LEAD(I.PARTY_ID) OVER (PARTITION BY I.FIRST_NM, I.MDDL_NM,   I.LAST_NM,  A.ZIP_CD ORDER BY I.FIRST_NM, I.MDDL_NM,I.LAST_NM, A.ZIP_CD) AS LEAD_PI,  MAX(I.PARTY_ID) OVER (PARTITION BY I.FIRST_NM, I.MDDL_NM, I.LAST_NM, A.ZIP_CD) AS MAX_PI  
        FROM INDVDL I JOIN PARTY_ADDR A  
        ON I.PARTY_ID = A.PARTY_ID  
       ) B  
  WHERE MIN_PI &lt;&gt; MAX_PI  
  AND MAX_PI &lt;&gt; nvl(LEAD_PI,0)
  </code></pre>