How to eliminate duplicate rows but the maximum va

Question

My query :

SELECT CHR,CHNO,CHSQ,
ROW_NUMBER () OVER(PARTITION BY CHNO ORDER BY CHSQ DESC ) TEMP,
CHSB,CHVR,CHRD
FROM WRPDAT.WSCLHP
WHERE CHADT > '20180901' 
AND CHSB ='R' 
AND CHB1 in ('L1', 'R2')
ORDER BY CHSQ

The value of TEMP must be the highest(ie 2 or 3 or 4) etc amongst the duplicate rows, and rest must be eliminated. If no duplicate occurs, the TEMP=1 must be in result

Sample Data

CHR    CHNO    CHSQ  TEMP CHSB  CHVR CHRD
F140    R11671  A11671  1   R   0   4
F140    R11671  A11671  2   R   1   4
T181    90391R  A90391  1   R   0   52
T181    90391R  A90391  2   R   1   38
M033    R02226  B02226  1   R   0   1
M033    R01674  C01674  1   R   0   31
T030    M47343  0M4734  1   R   1   26
T030    M47343  0M4734  2   R   2   22
T030    M58870  0M5887  1   R   0   26
T030    M59451  0M5945  1   R   0   17
T031    X22130  0X2213  1   R   0   98
T031    X22130  0X2213  2   R   1   98
T031    X22130  0X2213  3   R   2   98
T031    X43800  0X4380  1   R   0   7
T031    X43800  0X4380  2   R   1   7
T031    X48460  0X4846  1   R   0   19
C253    00049C  0X4849  1   R   0   4
T185    R02021  0X1211  1   R   2   42
C253    00162C  A12162  1   R   0   1
C253    00016C  VR1631  1   R   0   19
C253    00360C  CV1360  1   R   0   18
N036    00927R  A2E927  1   R   0   97
N036    00927R  A2E927  2   R   1   37
N036    00927R  A2E927  3   R   2   37

Desired Result

   CHR     CHNO    CHSQ  TEMP CHSB CHVR CHRD
    F140    R11671  A11671  2   R   1   4
    T181    90391R  A90391  2   R   1   38
    M033    R02226  B02226  1   R   0   1
    M033    R01674  C01674  1   R   0   31
    T030    M47343  0M4734  2   R   2   22
    T030    M58870  0M5887  1   R   0   26
    T030    M59451  0M5945  1   R   0   17
    T031    X22130  0X2213  3   R   2   98
    T031    X43800  0X4380  2   R   1   7
    C253    00049C  0X4849  1   R   0   4
    T185    R02021  0X1211  1   R   2   42
    C253    00162C  A12162  1   R   0   1
    C253    00016C  VR1631  1   R   0   19
    C253    00360C  CV1360  1   R   0   18
    N036    00927R  A2E927  3   R   2   37

My database: DB2 Tool: QMF

Answer 1

Got the desired result with this query !



        WITH PH AS(
        SELECT CHR,CHNO,CHSQ,
        ROW_NUMBER () OVER(PARTITION BY  CHNO ORDER BY CHSQ,CHVR DESC ) TEMP,
        CHSB,CHVR,CHRD
        FROM WRPDAT.WSCLHP
        WHERE CHADT > '20180901' 
        AND CHSB ='R' 
        AND CHB1 in ('L1', 'R2')
        )
        SELECT * 
        FROM PH A
        WHERE A.TEMP=1
        ORDER BY CHSQ