On 23 Feb, 04:11, "vipin" <vipinpaliwal1...@gmail.com> wrote:
> hi,
> i want to delete all the duplicate record in table
> Pls give me query

<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 23 Feb, 04:11, "vipin" <vipinpaliwal1...@gmail.com> wrote:
>
> > hi,
> > i want to delete all the duplicate record in table
> > Pls give me query
>
> Your table should have a key to prevent duplicates. Example:
>
> CREATE TABLE t1 (x INT NOT NULL, z INT NOT NULL /* NO KEY!! */);
>
> INSERT INTO t1 (x,z) VALUES (1,1);
> INSERT INTO t1 (x,z) VALUES (2,2);
> INSERT INTO t1 (x,z) VALUES (2,2);
> INSERT INTO t1 (x,z) VALUES (3,3);
>
> WITH t AS
> (SELECT x,z,ROW_NUMBER()
> OVER (PARTITION BY x,z ORDER BY x,z) AS r
> FROM t1)
> DELETE FROM t
> WHERE r>1;
>
> SELECT x,z FROM t1;
>
> -- Add a key
> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (x,z);
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

wrote:

>hi,
>i want to delete all the duplicate record in table
>Pls give me query

"vipin" <vipinpaliwal1980@gmail.com> wrote in message
news:1172203861.667815.179400@j27g2000cwj.googlegroups.com...
> hi,
> i want to delete all the duplicate record in table
> Pls give me query
>

On 25 Feb, 08:10, "Uri Dimant" <u...@iscar.co.il> wrote:
> vipinhttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
>
> "vipin" <vipinpaliwal1...@gmail.com> wrote in message
>
> news:1172203861.667815.179400@j27g2000cwj.googlegroups.com...
>
>
>
> > hi,
> > i want to delete all the duplicate record in table
> > Pls give me query- Hide quoted text -
>
> - Show quoted text -

> Out of interest, I've just found out that if you use the UNION
> function, it removes any duplicate values. Does anyone know if this
> would be a suitable (faster?) way of removing duplicates if you union
> a table by itself?

"Matt Urbanowski" <sales@matturbanowski.co.uk> wrote in message
news:1172502586.519419.55760@p10g2000cwp.googlegroups.com...
> On 25 Feb, 08:10, "Uri Dimant" <u...@iscar.co.il> wrote:
>> vipinhttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
>>
>> "vipin" <vipinpaliwal1...@gmail.com> wrote in message
>>
>> news:1172203861.667815.179400@j27g2000cwj.googlegroups.com...
>>
>>
>>
>> > hi,
>> > i want to delete all the duplicate record in table
>> > Pls give me query- Hide quoted text -
>>
>> - Show quoted text -
>
> Out of interest, I've just found out that if you use the UNION
> function, it removes any duplicate values. Does anyone know if this
> would be a suitable (faster?) way of removing duplicates if you union
> a table by itself?
>

<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> > Out of interest, I've just found out that if you use the UNION
> > function, it removes any duplicate values. Does anyone know if this
> > would be a suitable (faster?) way of removing duplicates if you union
> > a table by itself?
>
> Yes, UNION by definition removes duplicates. If you want to keepo duplicates, you use UNION ALL.
>
> I tried three versions on my machine, comparing DISTINCT, GROUP BY (all columns) and UNION. The two
> first produced the same plan (as expected), while the UNION produced a much worse plan. You can
> check execution plan for below (assuming SQL Server 2005):
>
> SELECT DISTINCT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
> SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate
> FROM Sales.SalesOrderDetail
>
> SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
> UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate
> FROM Sales.SalesOrderDetail
> GROUP BY SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
> SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate
>
> SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
> UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate
> FROM Sales.SalesOrderDetail
> UNION
> SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
> UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate
> FROM Sales.SalesOrderDetail
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>
> "Matt Urbanowski" <s...@matturbanowski.co.uk> wrote in message
>
> news:1172502586.519419.55760@p10g2000cwp.googlegroups.com...
>
>
>
> > On 25 Feb, 08:10, "Uri Dimant" <u...@iscar.co.il> wrote:
> >> vipinhttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
>
> >> "vipin" <vipinpaliwal1...@gmail.com> wrote in message
>
> >>news:1172203861.667815.179400@j27g2000cwj.googlegroups.com...
>
> >> > hi,
> >> > i want to delete all the duplicate record in table
> >> > Pls give me query- Hide quoted text -
>
> >> - Show quoted text -
>
> > Out of interest, I've just found out that if you use the UNION
> > function, it removes any duplicate values. Does anyone know if this
> > would be a suitable (faster?) way of removing duplicates if you union
> > a table by itself?- Hide quoted text -
>
> - Show quoted text -