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 stackoverflow.com/questions/11130964/… — TechDo
– TechDo, Commented Jan 31, 2013 at 12:17

Tim Schmelter · Accepted Answer · 2013-01-31 12:36:30Z

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, 2013 at 12:36

answered Jan 31, 2013 at 12:11

Tim Schmelter

462k79 gold badges718 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to remove repeated add log from table?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related