How to remove duplicates from this query?

How to remove duplicates from this query?

sql query remove duplicate rows based on one column
mysql remove duplicates from query
delete duplicate records in sql server using row_number
how to delete duplicate rows in sql server without using row_number
sql filter duplicates
delete duplicate rows in mysql
delete duplicate records in sql w3schools
how to remove duplicate records in table

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


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?

Why are there duplicates in the query results and how to minimize , results: Limit the number of output fields, especially one-to-many fields, such as phone number. Increase the number of filters. For example, when filtering on phone numbers, also filter on the phone type to limit it to one specific phone type. Scan your PC for Free and Remove Duplicate Files - Duplicate Sweeper


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.

Duplicate data often creeps in when multiple users add data to the database at the same time or if the database wasn't designed to check for duplicates. On the Design tab, click Run. Verify that the query returns the records that you want to delete. Click Design View and on the Design tab, click Delete. Access changes the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.


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;

This tutorial step by step shows you how to delete duplicate rows from a table in SQL Server. Third, query data from the sales.contacts table: SELECT  DISTINCT is useful in certain circumstances, but it has drawback that it can increase load on the query engine to perform the sort (since it needs to compare the result set to itself to remove duplicates) Below are alternate solutions : 1. Remove Duplicates Using Row_Number.


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

The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique. The group by clause can also be used to remove duplicates. In the Query Editor ribbon, click Remove Duplicates. Using the Query Editor context menu In the Query Editor ribbon, click Remove Duplicates. You can also right click and click Remove Duplicates.


SQL | Remove Duplicates without Distinct. DISTINCT is useful in certain circumstances, but it has drawback that it can increase load on the query engine to  One way to do this is to use query properties to remove duplicates from your mailing lists. For example, say you just performed a query on your Orders database to obtain the names and addresses of


Removes all rows from a Power Query table, in the Query Editor, where the values in the selected columns duplicate earlier values. Eliminating Duplicate Rows. When the result set from a SELECT statement contains duplicate rows, you may want to remove them and keep every row data to be unique for a column or combination of columns. You can use the DISTINCT or DISTINCTROW identifier to eliminate duplicate records.


Rather than spend time removing the duplicates one by one, rerun the query with the Unique Values property set to Yes. Follow these steps:. RANK function to SQL delete duplicate rows. We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row. In the following query, we use a RANK function with the PARTITION BY clause.