How To Take Away Duplicate Rows From A Tabular Array Inwards Sql

There are a dyad of ways to take duplicate rows from a tabular array inward SQL e.g. you lot tin utilization a temp tables or a window role similar row_number() to generate artificial ranking in addition to take the duplicates. By using a temp table, you lot tin commencement re-create all unique records into a temp tabular array in addition to and then delete all information from the master tabular array in addition to and then re-create unique records over again to the master table. This way, all duplicate rows volition live removed, but amongst large tables, this solution volition require additional infinite of the same magnitude of the master table. The instant approach doesn't require extra infinite every bit it removes duplicate rows straight from the table. It uses a ranking role like row_number() to assign a row number to each row.

By using partition by clause you lot tin reset the row numbers on a especial column. In this approach, all unique rows volition bring row number = 1 in addition to duplicate row volition bring row_number > 1, which gives you lot an slowly selection to take those duplicate rows. You tin do that yesteryear using a common tabular array expression (see T-SQL Fundamentals) or without it on Microsoft SQL Server.

No incertitude that SQL queries are the integral component of whatsoever programming chore interview which requires database in addition to SQL knowledge. The queries are every bit good really interesting to banking concern jibe candidate's logical reasoning ability.

Earlier, I bring shared a listing of frequently asked SQL queries from interviews in addition to this article is an extension of that. I bring shared lot of skilful SQL based occupation on that article in addition to users bring every bit good shared roughly first-class problems inward the comments, which you lot should look.

Btw, this is the follow-up interrogation of roughly other pop SQL interview question, how do you lot discovery duplicate records inward a table, which nosotros bring discussed earlier. This is an interesting interrogation because many candidates confuse themselves easily.

Some candidate says that they volition discovery duplicate yesteryear using grouping yesteryear in addition to printing refer which has counted to a greater extent than than 1, but when it comes to deleting this approach doesn't work, because if you lot delete using this logic both duplicate in addition to unique row volition acquire deleted.

This footling flake of extra especial e.g. row_number makes this occupation challenging for many programmers who don't utilization SQL on a daily basis. Now, let's run across our solution to delete duplicate rows from a table inward SQL Server.

Setup

Before exploring a solution, let's commencement create the tabular array in addition to populate amongst examine information to empathise both occupation in addition to solution better. I am using a temp tabular array to avoid leaving examine information into the database ane time nosotros are done. Since temp tables are cleaned upward ane time you lot closed the connectedness to the database, they are best suited for testing.

In our table, I bring simply ane column for simplicity, if you lot bring multiple columns in addition to then the Definition of duplicate depends whether all columns should live equal or roughly key columns e.g. name in addition to city tin live same for 2 unique persons. In such cases, you lot demand to extend the solution yesteryear using those columns on key places e.g. on a distinct clause inward the commencement solution in addition to on the sectionalization yesteryear inward instant solution.

Anyway, hither is our temp tabular array amongst examine data, it is carefully constructed to bring duplicates, you lot tin run across that C++ is repeated thrice piece Java is repeated twice inward the table.

-- create a temp tabular array for testing create tabular array #programming (name varchar(10));  -- insert information amongst duplicate, C++ is repeated 3 times, piece Java 2 times insert into #programming values ('Java'); insert into #programming values ('C++'); insert into #programming values ('JavaScript'); insert into #programming values ('Python'); insert into #programming values ('C++'); insert into #programming values ('Java'); insert into #programming values ('C++');  -- cleanup drop table #programming

Solution 1 - Use temp table

Yes, this is the most elementary but logical agency to take duplicate elements from a tabular array in addition to it volition piece of work across database e.g. MySQL, Oracle or SQL Server. The thought is to re-create unique rows into a temp table. You tin discovery unique rows yesteryear using distinct clause. Once unique rows are copied, delete everything from the master tabular array in addition to and then re-create unique rows again. This way, all the duplicate rows bring been removed every bit shown below.

-- removing duplicate using copy, delete in addition to copy select distinct refer into #unique from #programming delete from #programming; insert into #programming pick out * from #unique  -- banking concern jibe after select * from #programming  refer Java C++ JavaScript Python

You tin run across the duplicate occurrences of Java in addition to C++ bring been removed from the #programming temp table. This is yesteryear far the simplest solution in addition to every bit good quite slowly to empathise but it doesn't come upward to your hear without practicing. I advise solving roughly SQL puzzles from Joe Celko's classic book, SQL Puzzles in addition to Answers, Second Edition to prepare your SQL sense. It's a non bad practise mass to acquire in addition to primary SQL logic.

Solution 2 - Using row_number() in addition to derived table

The row_number() is ane of several ranking functions provided yesteryear SQL Server, It every bit good exists inward Oracle database. You tin utilization this role supply ranking to rows. You tin farther utilization sectionalization yesteryear to tell SQL server that what would live the window. This agency row number volition restart every bit shortly every bit a dissimilar refer comes upward but for the same name, all rows volition acquire sequential numbers e.g. 1, 2, 3 etc. Now, it's slowly to spot the duplicates inward the derived tabular array every bit shown inward the next example:

select * from (select *, row_number() OVER ( sectionalization by name social club by name) as rn from #programming) dups  name rn C++ 1 C++ 2 C++ 3 Java 1 Java 2 JavaScript 1 Python 1

Now, you lot tin remove all the duplicates which are aught but rows with rn > 1 , every bit done yesteryear next SQL query:

delete dups  from (select *, row_number() OVER ( sectionalization yesteryear refer order by name) as rn from #programming)  dups  WHERE rn > 1  (3 row(s) affected)

now, if you lot banking concern jibe the #programming tabular array over again at that spot won't live whatsoever duplicates.

select * from #programming name Java C++ JavaScript Python

Here is a prissy summary of all 3 ways to take duplicates from a  table using SQL:

Solution 3 - using CTE

The CTE stands for mutual tabular array expression, which is similar to derived table in addition to used to the temporary resultant laid that is defined inside the execution range of a unmarried SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Similar to derived table, CTE is every bit good non stored every bit an object in addition to lasts alone for the duration of the query. You tin rewrite the previous solution using CTE every bit shown below:

;with cte as (select row_number() over (partition by name social club by(select 0)) rn from #programming) delete from cte where rn > 1

The logic is just similar to the previous instance in addition to I am using pick out 0 because it's arbitrary which rows to save inward the lawsuit of a necktie every bit both contents same data. If you lot are novel to CTE in addition to then I advise reading T-SQL Fundamentals, ane of the best books to acquire SQL Server fundamentals.


That's all virtually how to take duplicate rows from a tabular array inward SQL. As I said, this is ane of the oft asked SQL queries, thus live gear upward for that when you lot acquire for your programming chore interview. I bring tested the query inward SQL Server 2008 in addition to they piece of work fine in addition to you lot mightiness demand to tweak them a footling flake depending upon the database you lot are going to utilization e.g. MySQL, Oracle or PostgreSQL. Feel gratis to post, if you lot human face upward whatsoever number piece removing duplicates inward Oracle, MySQL or whatsoever other database.

Further Learning
answer)

How to bring together 3 tables inward ane SQL query? (solution)

How to discovery all tabular array names inward a database? (query)

How do you lot create backup of tabular array or re-create of tabular array using SQL? (answer)

How do you lot discovery all customers who bring never ordered? (solution)

Can you lot write pagination query for Oracle using row_number? (query)

How do you lot discovery Nth highest salary of an employee using the correlated query? (solution)

SQL Puzzles in addition to Answers yesteryear Joe Celko (read)

Share this post