如何消除重复的行，但是结果中的这些行中的rownumber（）最大值？ [英] How to eliminate duplicate rows but the maximum value in the rownumber(),of these rows be in the result?

问题描述

我的查询：

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

在重复的行中，TEMP的值必须是最高的（即2或3或4），其余的必须消除。
如果没有重复，则结果必须为TEMP = 1

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

样本数据

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

所需结果

   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

我的数据库：DB2
工具：QMF

My database: DB2 Tool: QMF

推荐答案

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

这篇关于如何消除重复的行，但是结果中的这些行中的rownumber（）最大值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！