Monday, May 5, 2014

T-SQL: Remove duplicate rows

The following example partitions the query by columns foo and bar and generates a number for each sequence containing the same 'foo' and 'bar'. Then using common table expressions 'WITH cte', which stores temporary the result set in a table named 'cte' (or whatever you choose) and then uses the script 'DELETE cte where [RN] > 1' to remove and duplicate entries having more than one result set with the same 'foo' and 'bar' combination.

Be extra careful before using DELETE ;) I usually do a select query instead of a delete to see what I am about to delete and when i'm certain that the query is correct, I change it to a delete.


WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)

DELETE cte WHERE [rn] > 1

Source
http://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one

16 comments:

  1. I would like more information about this, because it is very nice. Thanks for sharing!
    facebook baixar
    baixar facebook
    baixar facebook gratis
    facebook movel baixar

    ReplyDelete

  2. افضل شركة تنظيف منازل بالدمام
    شركة تنظيف شقق بالدمام شركة تنظيف شقق بالدمام
    شركة مكافحة حشرات بالخبر شركة مكافحة حشرات بالخبر
    شركة رش مبيدات بالدمام شركة رش مبيدات بالدمام
    شركة تنظيف بالخبر شركة تنظيف بالخبر
    افضل شركة نقل عفش بالاحساء افضل شركة نقل عفش بالاحساء
    شركة كشف تسربات المياه بالاحساء شركة كشف تسربات المياه بالحساء

    ReplyDelete
  3. Get Shifting/Relocation Quotation from ###Packers and Movers Delhi. Packers and Movers Delhi 100% Affordable and Reliable ***Household Shifting Services. Compare Transportation Charges and Save Time, Verified and Trusted Packers and Movers in Delhi, Cheap and Safe Local, Domestic House Shifting @
    Packers and Movers Delhi

    ReplyDelete
  4. Packers and Movers Pune Provide High Quality ***Household Shifting, Home/Office Relocation, Insurance, Packing, Loading, ###Car Transportation Service Pune and High experiences, Top Rated, Safe and Reliable, Best and Secure Packers and Movers Pune Team List. Get ✔✔✔ Affordable Rate Charts and Compare Quotation and Save Money and Time .
    Packers And Movers Pune

    ReplyDelete


  5. شكرا معلومات مفيدة يمكنك زيارة ايضا لمعلومات قيمة اخرى قم بزيارة :

    شركة تنظيف خزانات بالمدينة المنورة
    افضل شركة تنظيف

    ReplyDelete
  6. This is really a pleasant and useful, containing all data furthermore greatly affect the new innovation. Much obliged for sharing it @ Packers And Movers Delhi
    Packers And Movers Greater Noida
    Packers And Movers Noida
    Packers And Movers Ghaziabad

    ReplyDelete
  7. Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ???Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @
    Packers And Movers Gurgaon

    ReplyDelete
  8. Spolu offers OnePlus, Mi, Vivo, Lenovo & iPhone all types od mobile mobiles repair service at your doorstep. All our phone repairs come with 6 months warranty, same day service. We'll send you a text with a link to download the app.
    You can download the mobile app here-
    Doorstep Mobile Repair Service in Noida

    ReplyDelete
  9. Orel State University which is also known as Government State Educational Institution of Higher Education is identified as a prime higher education Institution in the metropolis of Orel. Orel State University was founded in 1931 as an industrial-Pedagogical Institute. In 1932 Orel Pedagogical University became Orel State University. Orel State University is unique in many aspects.
    To get Admission and more information-
    Orel State Medical University

    ReplyDelete
  10. Orel State University is unique in many ways. It is the only classical university Orel, a member of the Association of classical Russian universities. It is the only institution in the region, which is training on a wide range of areas and specialties of higher and postgraduate education, training, re-training (more than 300).
    To Get more information and Admission-
    Orel State Medical University

    ReplyDelete
  11. Orel State University is unique in many aspects. Orel State University is the only classical university who is the member of the Association of classical universities of the Russian Federation and the only educational institution in the region which provide training on a wide range of areas and specialties of higher and postgraduate education, training, retraining.

    Orel State Medical University

    ReplyDelete
  12. Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔ ✔ ✔ Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @ Packers And Movers Jaipur

    ReplyDelete
  13. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete
  14. I simply would like to give a huge thumbs up for the good data you’ve Write here on this post. I shall be coming again to your weblog for extra soon. @ Packers and Movers Ahmedabad

    ReplyDelete
  15. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  16. Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges. @ Packers And Movers Bangalore

    ReplyDelete