August 28, 2008 at 9:33 am
i have a table that broken. there are some rows repeated. How may i delete the repeated rows
REFNOCOLUMN ........ ROWNOCOLUMN
2356 1
2356 2
2356 2 ---must delete this row all the other columns are same with the upper row
2356 3
2356 4
August 28, 2008 at 10:02 am
DELETE D
FROM
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY RefNoColumn, RowNoColumn ORDER BY RefNoColumn) AS RowID
    FROM YourTable
) D
WHERE RowID > 1
August 28, 2008 at 10:04 am
Simple way:
Delete From YourTable T
Where EXISTS( Select * from YourTable T2
Where T.REFNOCOLUMN = T2.REFNOCOLUMN
And T.ROWNOCOLUMN<T.ROWNOCOLUMN)
This leaves the record with the highest ROWNOCOLUMN and deletes all of the duplicates below/before it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 1:22 pm
thank's for your helps but they did not work because of i think my server is sql 2000
August 28, 2008 at 1:24 pm
Mine should work on SQL 2000. Did you try it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 1:27 pm
oopps wrong forum, sory again
August 28, 2008 at 1:29 pm
yes i tried, but "select * from Invoicetable T " is incorrect syntax
August 28, 2008 at 1:32 pm
Ok, my bad. Here is the corrected version for SQL 2000:
Delete From YourTable
Where EXISTS( Select * from YourTable T2
Where YourTable.REFNOCOLUMN = T2.REFNOCOLUMN
And YourTable.ROWNOCOLUMN<T.ROWNOCOLUMN)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 1:42 pm
thank you again, i think it's working i'll trie a few on it
August 28, 2008 at 2:02 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 2:05 pm
no it's working wrong,
Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)
is deleting all rows without rownumber 1
Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)
is deleting all rows without rownumber 6 and
Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO = T2.FATHARSIRANO)
is deleting all
TABLE FATHAR
FATHARREFNO|FATHARSIRANO
1 | 1
1 |1
1 |2
1 |2
1 |3
1 |3
1 |4
1 |2
1 |5
1 |5
1 |6
August 28, 2008 at 2:25 pm
yaman bas (8/28/2008)
no it's working wrong,Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)
is deleting all rows without rownumber 1
Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)
is deleting all rows without rownumber 6 and
I am sorry, but this makes no sense: both of these queries are the same, how can they be doing three different things?!? You must be doing something other than what you are telling us, because what you are describing is impossible.
Delete From FATHAR
Where EXISTS( Select * from FATHAR T2
Where FATHAR.FATHARREFNO = T2.FATHARREFNO
And FATHAR.FATHARSIRANO = T2.FATHARSIRANO)
is deleting all
Yes, of course it will. And this is not what I gave you.
Please provide some table defintions and sample data so that we can test this ourselves. See this link for instructions on how to provide this information: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 2:32 pm
OK, I think I see part of the problem: I misunderstood the role of the second column: you just want to eliminate all duplicates.
Easy enough:
Select Distinct *
Into #TempFATHAR
From FATHAR;
Delete From FATHAR;
Insert into FATHAR
Select * from #TempFATHAR
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 2:48 pm
Thank you very much RBarry, you are great, i was just working about the give you a sample table.
your query worked good on the sample table and i'll trie it on mine.
August 29, 2008 at 7:55 am
Even this works....
SET ROWCOUNT 1
DELETE YOURTABLE
FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE B WHERE A.COLNAME=B.COLNAME) >1
WHILE @@ROWCOUNT >0
DELETE YOURTABLE
FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE B WHERE A.COLNAME=B.COLNAME ) >1
SET ROWCOUNT 0
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply