sql query to delete only one duplicate row from table

Question

0.00/5 (No votes)

See more:

Hi,
I have a table with some duplicate rows in it.
i want to delete only one duplicate row.
for example i have 9 duplicate rows so should delete only one row and should show 8 remaining rows.

example

                date         calling   called  duration    timestampp
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121

from above date should delete only one row and should show 3 rows

2012-06-19 10:22:45.000	165	218	155	1.9         100
2012-06-19 10:22:45.000	165	218	155	1.9         100
2012-06-19 10:22:45.000	165	218	155	1.9         100

from above date should delete only one row and should show 2 rows

and etc..

Thanks and Regards
DSVS

Posted 20-Jun-12 4:46am

srinvas

Updated 20-Jun-12 5:49am

AmitGajjar

v2

Add a Solution

Comments

luisnike19 20-Jun-12 10:55am

Do you have an ID that identifies each record?

srinvas 20-Jun-12 11:02am

we dont have ID each record is identical with other row

date calling called duration timestampp

2012-06-19 10:22:45.000 165 218 155 1.9 121
2012-06-19 10:22:45.000 165 218 155 1.9 121

3 solutions

Solution 3

SQL

delete t
from   yourtable t
where  yourColumn = (select top 1 yourColumn from yourtable order by . . . )

Posted 20-Jun-12 5:53am

Sameer Alomari

Comments

Maciej Los 20-Jun-12 16:57pm

It should works, my 5!

Sameer Alomari 20-Jun-12 17:09pm

;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Accepted Answer · 2012-06-20T04:59:00

Follow the advice in the following article:

http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml[^]

However, instead of deleting every dulicate row, loop through each row. If it matches the previous row and your boolean flag is false, delete it and set a boolean flag to true. Then, if the next row matches, check the boolean flag and, since it is true, don't delete that row. When a row is different than the previous row, set the boolean flag to false. This will delete one of each duplicate row in your database.

luisnike19 · Accepted Answer · 2012-06-20T05:01:00

Solution 2

This will help you

http://www.sqlteam.com/article/deleting-duplicate-records[^]

http://chiragrdarji.wordpress.com/2007/07/23/delete-single-row-from-duplicate-rows-in-sql-server-2005-and-2000/[^]

Posted 20-Jun-12 5:01am

luisnike19

Comments

Maciej Los 20-Jun-12 16:53pm

Good links!
+5

sql query to delete only one duplicate row from table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us