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'.

Answer 1

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

Answer 2

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;

Answer 3

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.

Answer 4

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?