How to remove duplicates from this query?

Question

My query:

   WITH PH AS(
            SELECT CHR,CHNO,CHSQ,CHVR,CHSB
            ,ROW_NUMBER () OVER(PARTITION BY  CHNO ORDER BY CHSQ,CHVR DESC ) TEMP
            FROM WRPD.WSCL 
            WHERE CHAD > '20180901' 
            AND CHSB ='P' 
            AND CHB1 in ('L1', 'R2')
            ),
    DT AS(SELECT CHR,CHNO,CHSQ,CHVR,CHSB
            ,ROW_NUMBER () OVER(PARTITION BY  CHNO ORDER BY CHSQ,CHVR DESC ) TEMP
            FROM WRPD.WSCL
            WHERE CHAD > '20180901' 
            AND CHSB IN ('R','A','Q')
            AND CHB1 in ('L1', 'R2')
            )

SELECT * 
        FROM PH A
        WHERE A.TEMP=1
         UNION
        SELECT * 
        FROM DT B
        WHERE B.TEMP=1

Sample Data:
In the first 8 rows, the columns are duplicates,except values of CHVR,CHSB and Temp

CHR CHNO  CHSQ CHVR CHSB TEMP
A   F41  841    1   P   1
A   F41  841    0   R   2
B   447  147    1   P   1
B   447  147    0   R   2
C   742  742    1   P   1
C   742  742    0   R   2
D   231  135    3   P   1
D   231  135    2   R   2
E   749  417    0   A   1
E   775  153    0   P   1
E   775  153    0   A   1
E   178  833    1   Q   1
E   184  418    0   A   1
E   192  419    0   P   1
E   193  419    0   P   1
E   194  444    0   P   1
E   198  263    0   A   1
E   204  450    0   A   1
E   203  120    0   A   1

Desired Result:

I need the result with CHSB='P',which means: Basically if CHR=CHR AND CHNO=CHNO AND CHSQ=CHSQ, then display the values when CHSB='P', also CHVR of 'P' will be greater than CHVR of 'R'.

CHR CHNO CHSQ CHVR CHSB TEMP
A   F41  841    1   P   1
B   447  147    1   P   1
C   742  742    1   P   1
D   231 135    3    P   1
E   749 417    0    A   1
E   775 153    0    P   1
E   775 153    0    A   1
E   178 833    1    Q   1
E   184 418    0    A   1
E   192 419    0    P   1
E   193 419    0    P   1
E   194 444    0    P   1
E   198 263    0    A   1
E   204 450    0    A   1
E   203 120    0    A   1

I am not sure how and where to include the conditions in the query. Thanks in advance

EDIT : By this statement also CHVR of 'P' will be greater than CHVR of 'R' : i meant : For example,When we look at the sample data: the following two lines are duplicates of each other:

CHR CHNO  CHSQ CHVR CHSB TEMP
A   F41  841    1   P     1
A   F41  841    0   R     2

Now i want the rows with CHSB='P' eliminated. But three columns differ in this duplicates which are :

    CHVR=Version
   ,CHSB=Status
   ,TEMP=Temporary Value

Now, the following condition will always be true: In a duplicate row when Status='P' Version='1' and Status='R' Version='0'

This implies that value of version (1) for status='P' is greater than value of version(0) for status='R'.

This question is part of :[LINK] https://stackoverflow.com/questions/52841915/how-to-combine-three-queries AND [LINK] https://stackoverflow.com/questions/52824889/how-to-eliminate-duplicate-rows-but-the-maximum-value-in-the-rownumber-of-thes — , Oct 22 '18 at 23:19
Your query is not valid, you have a `;` where you probably want a `,`, and are missing a `UNION` between the selects. — Paul Vernon, Oct 23 '18 at 06:09
Your conditions of choosing a record from a group with equal (CHR, CHNO, CHSQ) are not clear enough. What's the meaning of "also CHVR of 'P' will be greater than CHVR of 'R'"? Why to use this condition, if we must select CHSB='P' if it exists? How to select the row needed if we don't have CHSB='P', but multiple rows with other different CHSBs? — Mark Barinstein, Oct 23 '18 at 07:49
@MarkBarinstein "CHVR of 'P' will be greater than CHVR of 'R'" means in the row where CHSB='P' the value of CHVR will be always greater than the the row with the value of CHVR ,where CHSB='R'. provided they are duplicates. Sorry for the confusion — , Oct 23 '18 at 17:45
Much better to edit your question rather than try to correct it in comments. It is still very unclear what you mean. Maybe if your example data was smaller/better it would help.. https://stackoverflow.com/help/how-to-ask — Paul Vernon, Oct 23 '18 at 17:47
Well I’m still confused. Sounds like you need to filter on a `ROW_NUMBER () OVER(PARTITION BY (CHR, CHNO, CHSQ ORDER BY CHVR)` or similar. Maybe post a new question with a _minimal reproducible_ question with simple column names, sample data and your best attempt at the SQL needed. — Paul Vernon, Oct 24 '18 at 06:24

score 1 · Answer 1 · answered Oct 24 '18 at 18:30

I got the desired result with this query :

   WITH PH AS(
            SELECT CHR,CHNO,CHSQ,CHVR,CHSB
            ,ROW_NUMBER () OVER(PARTITION BY  CHNO ORDER BY CHSQ,CHVR DESC ) TEMP
            FROM WRPD.WSCL 
            WHERE CHAD > '20180901' 
            AND CHSB ='P' 
            AND CHB1 in ('L1', 'R2')

            **UNION
            SELECT B.CHR,B.CHNO,B.CHSQ,B.CHVR,B.CHSB
            ,ROW_NUMBER () OVER(PARTITION BY  B.CHNO ORDER BY B.CHSQ,B.CHVR DESC ) TEMP
            FROM WRPD.WSCL A JOIN WRPD.WSCL B
            WHERE B.CHAD > '20180901'
             AND B.CHB1 in ('L1', 'R2')
             AND A.CHB1 in ('L1', 'R2') 
             AND A.CHSB ='P'
             AND A.CHSB ='R'
             AND CASE WHEN A.CHNO=B.CHNO AND A.CHVR>B.CHVR THEN 0
                     WHEN A.CHNO<>B.CHONO THEN 0 ELSE 1 END=1**

            ),
    DT AS(SELECT CHR,CHNO,CHSQ,CHVR,CHSB
            ,ROW_NUMBER () OVER(PARTITION BY  CHNO ORDER BY CHSQ,CHVR DESC ) TEMP
            FROM WRPD.WSCL
            WHERE CHAD > '20180901' 
            AND CHSB IN ('R','A','Q')
            AND CHB1 in ('L1', 'R2')
            )

SELECT * 
        FROM PH A
        WHERE A.TEMP=1
         UNION
        SELECT * 
        FROM DT B
        WHERE B.TEMP=1

score 0 · Answer 2 · answered Oct 22 '18 at 23:03

select chr, chrno, chsq, chvr, chsb, temp 
from mytable 
where chsb = 'P' or (chr, chrno, chsq) not in 
     (select chr, chrno, chsq from mytable where chsb = 'P')

This can be run on your output table after you are finished adding all the records you'd like to see in there. This assumes all dupes consist of exactly one chsb='P' and one chsb=SomethingElse. If three record dupe sets are possible, or dupes involving chsb other than 'P' are possible, you would need to set up a hierarchy to determine which record you want returned. You may have to name the tables and qualify the field names with them to remove ambiguity. Also, why are you getting temp=2 in your results when you specified temp=1 in your select statement?

when i added your condition to the query i got this error : [LINK] https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/n666.html — , Oct 22 '18 at 23:21
I mentioned naming the tables and qualifying the fields. Did you try that? — Ryan, Oct 23 '18 at 17:19

score 0 · Answer 3 · answered Oct 23 '18 at 17:37

Is this a trick question? To get your required result from your input data, you just need to say WHERE CHSB <> 'R'

WITH T(CHR,CHNO,CHSQ,CHVR,CHSB,TEMP) AS
(VALUES
 ('A','F41',841,1,'P',1)
,('A','F41',841,0,'R',2)
,('B','447',147,1,'P',1)
,('B','447',147,0,'R',2)
,('C','742',742,1,'P',1)
,('C','742',742,0,'R',2)
,('D','231',135,3,'P',1)
,('D','231',135,2,'R',2)
,('E','749',417,0,'A',1)
,('E','775',153,0,'P',1)
,('E','775',153,0,'A',1)
,('E','178',833,1,'Q',1)
,('E','184',418,0,'A',1)
,('E','192',419,0,'P',1)
,('E','193',419,0,'P',1)
,('E','194',444,0,'P',1)
,('E','198',263,0,'A',1)
,('E','204',450,0,'A',1)
,('E','203',120,0,'A',1)
)
SELECT CHR,CHNO,CHSQ,CHVR,CHSB,TEMP FROM T
WHERE CHSB <> 'R'


 CHR CHNO CHSQ CHVR CHSB TEMP
 --- ---- ---- ---- ---- ----
 A   F41   841    1 P       1
 B   447   147    1 P       1
 C   742   742    1 P       1
 D   231   135    3 P       1
 E   749   417    0 A       1
 E   775   153    0 P       1
 E   775   153    0 A       1
 E   178   833    1 Q       1
 E   184   418    0 A       1
 E   192   419    0 P       1
 E   193   419    0 P       1
 E   194   444    0 P       1
 E   198   263    0 A       1
 E   204   450    0 A       1
 E   203   120    0 A       1

If this is not what you want. I suggest you delete this question, and try to ask it is a more clear, minimal and understandable way.

I am sorry for the confusion, but the actual result must have other conditions where CHSB='R'. also i am unable to delete the question ! — , Oct 23 '18 at 17:41

score 0 · Answer 4 · answered Oct 24 '18 at 18:12

with a (CHR, CHNO, CHSQ, CHVR, CHSB, TEMP) as (values
  ('A', 'F41', 841, 1, 'P', 1)
, ('A', 'F41', 841, 0, 'R', 2)
, ('B', '447', 147, 1, 'P', 1)
, ('B', '447', 147, 0, 'R', 2)
-- ...
, ('E', '203', 120, 0, 'A', 1)
)
select CHR, CHNO, CHSQ, CHVR, CHSB, TEMP
from (
select a.*, rownumber() over (partition by CHR, CHNO, CHSQ order by case CHSB when 'P' then 0 else 1 end) rn_
from a
) 
where rn_=1;

How to remove duplicates from this query?

4 Answers4