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

4 comments:


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

    ReplyDelete
  2. 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
  3. 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