title: Oracle Max value via join two tables
body: <p>Oracle Max value via join two tables what am I doing wrong?</p>
  
  <p>tblKG:</p>
  
  <pre><code>KG_USER    KG_DATE    KG_TK_NO    KG_SQ_NO    KG_CNG_IND
  ---------------------------------------------------------
  123456   200820     1           1           B
  123456   200820     2           1           A
  123456   200820     3           1           A
  123456   200820     4           1           I
  123456   200820     4           2           F
  123456   200820     4           3           I
  123456   200820     4           4           F
  123456   200820     4           5           I
  123456   200820     4           6           F
  123456   200820     4           7           A
  </code></pre>
  
  <p>tblKN:</p>
  
  <pre><code>KN_USER    KN_DATE     KN_SQ_NO    KN_SEQ_NUMB
  --------------------------------------------------
  123456      200820      1           01
  123456      200820      2           01
  123456      200820      3           01
  123456      200820      4           07
  </code></pre>
  
  <p>VIEW:</p>
  
  <pre><code>SELECT
          N.KN_DATE AS DATE,
          N.KN_USER AS USER,
          N.KN_SEQ_NO,    
          G.KG_TK_NO,
          G.KG_DATE,
          G.KG_USER,
          G.KGCNG_IND,                          
          G.MAX_SEQ_NO                              
   FROM               
          KN N,        
          (SELECT 
            KG_TK_NO, 
            KG_DATE,   
            KG_USER,       
            KG_CNG_IND AS KGCNG_IND,
            MAX(KG_SEQ_NO)  AS MAX_SEQ_NO        
       FROM
        KG
        GROUP BY KG_TK_NO, KG_DATE, KG_USER, KG_CNG_IND
      )  G
  WHERE      
          N.KN_DATE = G.KG_DATE    
         AND N.KN_USER = G.KG_USER           
         AND N.KN_SEQ_NO = G.KG_TK_NO
  </code></pre>
  
  <p>RESULT:</p>
  
  <pre><code>DATE      USER        KN_SEQ_NO   KG_TK_NO    KGCNG_IND   MAX_SEQ_NO
  200820  123456      1           1           B           1
  200820  123456      2           2           A           1
  200820  123456      3           3           A           1
  200820  123456      4           4           A           7
  200820  123456      4           4           F           6
  200820  123456      4           4           I           5
  </code></pre>
  
  <p>Expected:</p>
  
  <pre><code>DATE      USER        KN_SEQ_NO   KG_TK_NO    KGCNG_IND   MAX_SEQ_NO
  200820    123456      1           1           B           1
  200820    123456      2           2           A           1
  200820    123456      3           3           A           1
  200820    123456      4           4           A           7
  </code></pre>