How to remove repeated add log from table?

Question

I have a log table where multiple rows have been included with ADD status ("status = 0" means "add").

How to remove status = 0 but it must be unrepeated value. For example each transaction id (tid) must have only one value with status = 0.

For example;

id tid       tblname           type     status 
1   101     x                   U              0
2   101     x                   U              0
3   102     x                   U              0
4   102     x                   U              0
5   102     x                   U              0

Must return:

id  tid     tblname       type           status 
1   101     x            U              0
3   102     x            U              0

How can I do that?

Possible duplicate of http://stackoverflow.com/questions/11130964/delete-duplicate-rows-in-sql-server-2010 — TechDo, Jan 31 '13 at 12:17
possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — loki, Jan 31 '13 at 12:19
possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Martin Smith, Mar 03 '14 at 00:18

Tim Schmelter · Accepted Answer · 2013-01-31T12:36:30.537

2

With a cte and ROW_NUMBER:

WITH x AS (SELECT rn = Row_number() 
                       OVER( 
                         partition BY tid 
                         ORDER BY id) 
         FROM   dbo.log
         WHERE  status = 0 
                AND tblname = 'X' 
                AND type = 'U') 
DELETE FROM  x 
WHERE  rn > 1

edited Jan 31 '13 at 12:36

answered Jan 31 '13 at 12:11

Tim Schmelter

450,073
74
686
939

How to remove repeated add log from table?

1 Answers1