CODEWITHSUNDEEP
t-sqlsql-server-2012duplicatesrankrow-number
stax1001
stax1001

Reputation: 1

Removing duplicates in SQL Server 2012 by grouping data with multiple rules

I hope you can help me.

I have a table of data in a SQL database (Table1) which contains information in 5 columns (1,2,3,4,5).

I require a query which does the following:

For example:

1,2,3,4,5

a,a,a,b,b

a,a,a,b,b

a,a,a,b,c

Here, we would keep only the second row - 'a,a,a,b,b' as there are two of these duplicated rows and one of the other, 'a,a,a,b,c'. If there were two duplicated rows of both types of data, we would keep the one most recently entered (a,a,a,b,c).

I've been attempting to solve this using a combination of RANK(), ROW_NUMBER() and COUNT() without success, as i'm relatively new to SQL. I've been using a combination of the following threads:

how to select the most frequently appearing values?

Select first row in each GROUP BY group?

SQL Query Select first rank 1 row From Multiple ranks/Group

I hope this explanation has been clear.

Thanks for reading!

Upvotes: 0

Views: 115

Answers (1)

csarwi
csarwi

Reputation: 35

guess this should help: 

with cte as (
select 
     column1, 
     column2,
     column3,
     column4,
     column5, 
     ROW_NUMBER() OVER(PARTITION BY Column1, Column2, Column3, Column4, Column5 ORDER BY Column4 DESC) AS R
 from 
     YourTable
)

select * from cte where r = 1

this is the way i always remove my duplicates ... hope this helps ...

Upvotes: 1

Related Questions