SQL Server Performance Forum – Threads Archive
deleting duplicate rows
Hi, I am a new SQL user so please excuse me if I don’t make much sense. I have returned data from a number of tables into one data set and I would like to be able to remove the rows where the employeeid and name are repeated so that only one instance of the employeeid remains. And my second question is similar, I want to be able to remove a first row from a data set whereby the employeeid and name are the same in both rows and the startdate in the second row is + 1 day or the same as the enddate on the first row. eg. ID Name Start End1 Jo 01/01/06 05/01/06 remove this row
1 Jo 06/01/06 19/01/06 I hope this makes at least a little bit of sense. Thanks heaps for your help!
Duncan
deletet
fromyourtable t
wheret.Start<> (select max(Start) from yourtable x
where x.ID = t.ID
andx.Name= t.Name) KH
Thanks for your help. I am having problems applying it to my existing query. Can I just add the script posted to the end of my query and replace ‘yourtable’ with the name of the primary table I am drawing data from? Duncan
yes. replace ‘yourtabe’ with your actual table name.
KH
]]>