CODEWITHSUNDEEP
oracle-databaseview
AhsenBaig
AhsenBaig

Reputation: 497

Oracle Max value via join two tables

Oracle Max value via join two tables what am I doing wrong?

tblKG:

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

tblKN:

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

VIEW:

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

RESULT:

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

Expected:

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

Upvotes: 3

Views: 5219

Answers (2)

angus
angus

Reputation: 2367

If you want to get the maximum value, you use MAX and GROUP BY. But you don't really want that, you want to get the row with the maximum value.

For that, you need to rank the rows according to the column you are interested in, and then just use the first one.

SELECT * FROM
 (SELECT 
      KG_TK_NO, 
      KG_DATE,   
      KG_USER,       
      KG_CNG_IND,
      KG_SEQ_NO,
      RANK() OVER (PARTITION BY KG_TK_NO, KG_DATE, KG_USER
                   ORDER BY KG_SEQ_NO DESC) AS R
  FROM KG)
WHERE R = 1

Upvotes: 4

Andrew Lazarus
Andrew Lazarus

Reputation: 19302

Suspect from your expected output you do not want fourth variable of GROUP BY.

GROUP BY KG_TK_NO, KG_DATE, KG_USER, ->KG_CNG_IND<-

and that you want MAX_SEQ_NO in this clause instead of G.KG_TK_NO

N.KN_SEQ_NO = G.KG_TK_NO

By the way, your column names are a little difficult to work with. I don't find the KG_ prefix style useful although it is popular. But the rest of the names are too short.

Upvotes: 2

Related Questions