How to delete duplicated rows

Sometimes we need to duplicated rows in a table. We may use row_number() function.

This example is to delete rows with duplicated email_addr.

delete users where rowid in (
select rid from
(select rowid rid,row_number() over(partition by email_addr order by userid desc) rn from users)
where rn > 1) ;

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How to delete duplicated rows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: