Prev: Particular query problem
Next: Leading and trailing spaces trimmed with SQL XML
From: fniles on 23 Apr 2010 15:06
Thanks

Is it possible to create a query to delete the duplicate records, but leave
1 of the record in the database ?
For ex: if I have 2 records where SequenceNumber = '19940103-0000' and
Symbol = 'EUR/USD', I would like to delete 1 of the record

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:acmdnSaVBNePSkzWnZ2dnUVZ_j4AAAAA(a)speakeasy.net...
>
> Here is one solution:
>
> SELECT SequenceNumber, CommodityCode, Symbol, MonthYear, cnt
> FROM (
> SELECT SequenceNumber, CommodityCode, Symbol, MonthYear,
> COUNT(*) OVER(PARTITION BY SequenceNumber, Symbol) AS cnt
> FROM dbo.TickData1Day) AS T
> WHERE cnt > 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 23 Apr 2010 17:40
Yes:

WITH Dups AS (
SELECT ROW_NUMBER() OVER(PARTITION BY SequenceNumber, Symbol ORDER BY (SELECT NULL)) AS rk
FROM dbo.TickData1Day)
DELETE FROM Dups
WHERE rk > 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: fniles on 26 Apr 2010 10:14
Great ! Thanks !

That works in SQL 2005, but is there a similar syntax for SQL 2000 ?
When I run this on SQL 2000, it says
Incorrect syntax near the keyword 'WITH'.
'ROW_NUMBER' is not a recognized function name.
Line 2: Incorrect syntax near ')'.

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:acmdnSGVBNdGjk_WnZ2dnUVZ_j6dnZ2d(a)speakeasy.net...
> Yes:
>
> WITH Dups AS (
> SELECT ROW_NUMBER() OVER(PARTITION BY SequenceNumber, Symbol ORDER BY
> (SELECT NULL)) AS rk
> FROM dbo.TickData1Day)
> DELETE FROM Dups
> WHERE rk > 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 26 Apr 2010 10:46
Here is SQL Server 2000 example that will leave the latest based on date and delete all other duplicates:

DELETE FROM TickData1Day
WHERE EXISTS(SELECT *
FROM TickData1Day AS D
WHERE D.SequenceNumber = TickData1Day.SequenceNumber
AND D.Symbol = TickData1Day.Symbol
AND D.[Date] > TickData1Day.[date]);

--
Plamen Ratchev
http://www.SQLStudio.com
From: fniles on 26 Apr 2010 11:38
Thank you.
Your query assumes that the [date] is different between the records.
How about those records whose data for each column is the same ? They are
the exact duplicate.
Can we delete them except 1 of them in SQL 2000 ?

Thanks again


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:cPednYUy04XVOkjWnZ2dnUVZ_gAAAAAA(a)speakeasy.net...
> Here is SQL Server 2000 example that will leave the latest based on date
> and delete all other duplicates:
>
> DELETE FROM TickData1Day
> WHERE EXISTS(SELECT *
> FROM TickData1Day AS D
> WHERE D.SequenceNumber = TickData1Day.SequenceNumber
> AND D.Symbol = TickData1Day.Symbol
> AND D.[Date] > TickData1Day.[date]);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Particular query problem
Next: Leading and trailing spaces trimmed with SQL XML