CODEWITHSUNDEEP
sqlsql-serverduplicates
Den
Den

Reputation: 379

How to delete duplicate record which has one unique column

I have a table like this.

enter image description here

How can I remove the records "Jimmy" and "Kenneth" which has greater Month values.

Thank you.

Upvotes: 1

Views: 95

Answers (5)

Rajeesh Menoth
Rajeesh Menoth

Reputation: 1750

Use this code..

WITH tblTemp as(SELECT ROW_NUMBER() Over(PARTITION BY Name,Post ORDER BY Name)As RowNumber,* FROM <table_name>)DELETE FROM tblTemp where RowNumber >1

Upvotes: 0

Pippi
Pippi

Reputation: 323

if month is a column of numbers try this :

DELETE table
WHERE  month = (SELECT Max(month)
                FROM   table
                WHERE  name = 'Jimmy')
       AND name = 'Jimmy';

DELETE table
WHERE  month = (SELECT Max(month)
                FROM   table
                WHERE  name = 'Kenneth')
       AND name = 'Kenneth';

Upvotes: 2

Nightmaresux
Nightmaresux

Reputation: 538

You can use over partition to seperate the biggest values in Month:

SELECT *
FROM   (SELECT *,
               Row_number()OVER(PARTITION BY name, Post
                   ORDER BY Month DESC) AS RowNumber
        FROM   TABLE)
WHERE  rownumber = 1

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Use CTE to delete the duplicate records

;with cte as
(
select Rn=row_number()over(partition by name,post order by month ASC),* 
from yourtable 
)
delete from cte where rn>1

Upvotes: 4

jarlh
jarlh

Reputation: 44776

delete from tablename t1 where exists (select 1 from tablename t2
                                       where t1.name = t2.name
                                         and t1.month > t2.month)

But why doesn't take year into consideration? Why not use a date datatype? What if two Jimmys from same month? Why no unique constraint if no duplicates allowed?

Upvotes: 1

Related Questions