delete distinct by column sQL

Question

I want to delete min Id if there is multiple employeeId. How can i do that in sQL query? Attacted my SQL query result

delete @CompPayDetEE where exists (select 1 from @CompPayDetEE group by AppEmployeeId )

SQL Result

You write code. That's your job, we (maybe) try help fix whatever you end up writing. — Marc B, Oct 27 '16 at 15:12
please paste data as text and show expected result...sample sql fiddle will help a lot — TheGameiswar, Oct 27 '16 at 15:12
http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server — UltraCommit, Oct 27 '16 at 15:15

score 0 · Accepted Answer · edited Oct 27 '16 at 15:36

0

This should work. I am retaining all max ids and deleting the remaining rows:

DELETE FROM Yourtable
  WHERE id NOT IN 
        (
          SELECT MAX(id)
            FROM Yourtable
           GROUP BY AppEmployeeID
         );

edited Oct 27 '16 at 15:36

krlzlx

5,752
14
47
55

answered Oct 27 '16 at 15:15

Teja

13,214
36
93
155

This should work... I am retaining all max ids and deleting the remaining rows.... – Teja Oct 27 '16 at 15:29

score 0 · Answer 2 · answered Oct 27 '16 at 15:21

declare  @Table table(ID int, EmployID varchar(10), TaxAmount int, TaxableAmount int)
insert into @Table values
(1, '1',    1,      0)
,(2, '1',    13,     1)
,(3, '3',    52,     4)


WITH cte AS(
    SELECT *,
            ROW_NUMBER() OVER(PARTITION BY EmployID ORDER BY id desc) as RN
    FROM @Table
)
DELETE cte
 WHERE RN <> 1;

SELECT * from @Table;

score 0 · Answer 3 · answered Oct 27 '16 at 15:31

0

delete @CompPayDetEE
where Id in(    select IdToDelete = min(Id)
                from @CompPayDetEE
                group by AppEmployeeId
                having count(*)>1)

answered Oct 27 '16 at 15:31

Marta B

438
2
9

delete distinct by column sQL

3 Answers3