CODEWITHSUNDEEP
sql
yzhang
yzhang

Reputation: 541

Deleting partly duplicate rows in a table

I have a table, with rowid, userid, productid, and times. once I ordered the table by userid and time. It looks like:

u1, t1, p1
u1, t2, p1
u1, t3, p1
u1, t4, p2
u1, t5, p2
u1, t6, p3
u2, t7, p1
u2, t8, p1
u2, t9, p2
u2, t10,p3
 .....

I want to delete those rows whose product id is same as the previous row.
therefore the final table should be :

u1, t1, p1
u1, t4, p2
u1, t6, p3
u2, t7, p1
u2, t9, p2
u2, t10,p3
.....

How can I do this in SQL? Many thanks

Upvotes: 0

Views: 124

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

Simplified version
Turns out it is for SQL Server, and you can delete from the CTE directly in SQL Server (as hinted by @Royi Namir in the comments below):

;WITH x AS (
    SELECT rowid,
           rownumber() OVER (PARTITION BY user_id,productid ORDER BY times) AS rn
    FROM   tbl
    )
DELETE FROM x
WHERE  rn > 1;

Simplified live demo at sqlfiddle.
Much like this one.

Upvotes: 1

Royi Namir
Royi Namir

Reputation: 148534

;WITH x AS (
    select rowid,  rownumber() 
            over (partition by userid,productid order by rowid) as rn     
    FROM   tbl
    )
DELETE FROM tbl
    WHERE  tbl.rowid= x.rowid
AND    x.rn > 1

Upvotes: 0

edepperson
edepperson

Reputation: 1035

DECLARE @tmp TABLE( prod VARCHAR(10));

INSERT INTO @tmp SELECT DISTINCT PROD FROM tblSource

SELECT TOP 1 FROM tblSource a
JOIN @tmp b ON a.product = b.prod
ORDER BY UserId, TimeId

my bad, I didn't read the question completely, since you are wanting to delete, you might want to use Royi's answer. OTH, you could put my selection into a second temp, then delete in the main table on a left join where the tmp2 is null

Upvotes: 0

Related Questions