0

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

  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group). Flipping the sort direction of the windowing clause (`OVER(PARTITION BY CHNO ORDER BY CHSQ ASC)`) would put the minimum value at `TEMP = 1`, consistently. – Clockwork-Muse Oct 15 '18 at 21:31
  • TEMP can have values =2,3,4, How do i display those values? –  Oct 15 '18 at 21:42
  • ...Why do you want to display those values? What purpose does it serve? Should you be using a windowed `COUNT()` (if supported)? Given the answer you posted, should you just be looking for the greatest `CHVR` value? – Clockwork-Muse Oct 15 '18 at 22:24
  • because the TEMP value represents the latest versions for specific CHNO. So in order to eliminate (older) duplicate versions TEMP has to be the maximum. –  Oct 15 '18 at 22:26

1 Answers1

0
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
  • you can mark your own question as answered if you are happy with your own answer ;-) – Paul Vernon Oct 15 '18 at 22:29
  • I am unable to do it !! –  Oct 15 '18 at 22:32
  • Uh, having looked at your dataset, do you even need `CHSQ` in that ordering at all? If supported by the database, should you switch to a windowed maximum? Your comment implied that the actual value of `TEMP` was important, but here it would be constant, and so not (likely to be) relevant. – Clockwork-Muse Oct 15 '18 at 22:35
  • The max() function didn't work at all.So, i had to use the window function. The CHSQ order is needed,to verify no occurrence of duplicates, ie to check the query and the value of TEMP is basically numbering given to value of CHSQ. –  Oct 15 '18 at 22:42