How To Take Duplicate Rows From A Tabular Array Inward Sql

There are a distich of ways to withdraw duplicate rows from a tabular array inwards SQL e.g. y'all tin usage a temp tables or a window portion similar row_number() to generate artificial ranking too withdraw the duplicates. By using a temp table, y'all tin outset re-create all unique records into a temp tabular array too and then delete all information from the master copy tabular array too and then re-create unique records over again to the master copy 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 copy table. The instant approach doesn't require extra infinite every bit it removes duplicate rows direct from the table. It uses a ranking portion like row_number() to assign a row number to each row.

By using partition by clause y'all tin reset the row numbers on a exceptional column. In this approach, all unique rows volition bring row number = 1 too duplicate row volition bring row_number > 1, which gives y'all an tardily pick to withdraw those duplicate rows. You tin do that past times using a common tabular array expression (see T-SQL Fundamentals) or without it on Microsoft SQL Server.

No uncertainty that SQL queries are the integral portion of whatsoever programming labor interview which requires database too SQL knowledge. The queries are every bit good rattling interesting to depository fiscal establishment represent candidate's logical reasoning ability.

Earlier, I bring shared a listing of frequently asked SQL queries from interviews too this article is an extension of that. I bring shared lot of proficient SQL based work on that article too users bring every bit good shared or therefore fantabulous problems inwards the comments, which y'all should look.

Btw, this is the follow-up enquiry of or therefore other pop SQL interview question, how do y'all abide by duplicate records inwards a table, which nosotros bring discussed earlier. This is an interesting enquiry because many candidates confuse themselves easily.

Some candidate says that they volition abide by duplicate past times using grouping past times too 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 y'all delete using this logic both duplicate too unique row volition acquire deleted.

This footling fleck of extra exceptional e.g. row_number makes this work challenging for many programmers who don't usage SQL on a daily basis. Now, let's run into our solution to delete duplicate rows from a table inwards SQL Server.

Setup

Before exploring a solution, let's outset create the tabular array too populate amongst evidence information to empathize both work too solution better. I am using a temp tabular array to avoid leaving evidence information into the database i time nosotros are done. Since temp tables are cleaned upward i time y'all unopen the connector to the database, they are best suited for testing.

In our table, I bring only i column for simplicity, if y'all bring multiple columns too then the Definition of duplicate depends whether all columns should live equal or or therefore key columns e.g. name too city tin live same for ii unique persons. In such cases, y'all demand to extend the solution past times using those columns on key places e.g. on a distinct clause inwards the outset solution too on the sectionalisation past times inwards instant solution.

Anyway, hither is our temp tabular array amongst evidence data, it is carefully constructed to bring duplicates, y'all tin run into that C++ is repeated thrice spell Java is repeated twice inwards 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, spell 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 uncomplicated but logical means to withdraw duplicate elements from a tabular array too 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 abide by unique rows past times using distinct clause. Once unique rows are copied, delete everything from the master copy tabular array too 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 too copy select distinct refer into #unique from #programming delete from #programming; insert into #programming direct * from #unique  -- depository fiscal establishment represent after select * from #programming  refer Java C++ JavaScript Python

You tin run into the duplicate occurrences of Java too C++ bring been removed from the #programming temp table. This is past times far the simplest solution too every bit good quite tardily to empathize but it doesn't come upward to your heed without practicing. I propose solving or therefore SQL puzzles from Joe Celko's classic book, SQL Puzzles too Answers, Second Edition to prepare your SQL sense. It's a non bad practise mass to larn too main SQL logic.

Solution 2 - Using row_number() too derived table

The row_number() is i of several ranking functions provided past times SQL Server, It every bit good exists inwards Oracle database. You tin usage this portion furnish ranking to rows. You tin farther usage sectionalisation past times to tell SQL server that what would live the window. This means row number volition restart every bit presently every bit a unlike refer comes upward but for the same name, all rows volition acquire sequential numbers e.g. 1, 2, 3 etc. Now, it's tardily to spot the duplicates inwards the derived tabular array every bit shown inwards the next example:

select * from (select *, row_number() OVER ( sectionalisation 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, y'all tin remove all the duplicates which are nix but rows with rn > 1 , every bit done past times next SQL query:

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

now, if y'all depository fiscal establishment represent the #programming tabular array over again in that location won't live whatsoever duplicates.

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

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

Solution 3 - using CTE

The CTE stands for mutual tabular array expression, which is similar to derived table too used to the temporary number 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 too lasts exclusively 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 illustration too I am using direct 0 because it's arbitrary which rows to save inwards the lawsuit of a necktie every bit both contents same data. If y'all are novel to CTE too then I propose reading T-SQL Fundamentals, i of the best books to larn SQL Server fundamentals.


That's all nigh how to withdraw duplicate rows from a tabular array inwards SQL. As I said, this is i of the oft asked SQL queries, therefore live ready for that when y'all become for your programming labor interview. I bring tested the query inwards SQL Server 2008 too they piece of work fine too y'all mightiness demand to tweak them a footling fleck depending upon the database y'all are going to usage e.g. MySQL, Oracle or PostgreSQL. Feel gratuitous to post, if y'all human face upward whatsoever number spell removing duplicates inwards Oracle, MySQL or whatsoever other database.

Further Learning
answer)

How to bring together 3 tables inwards i SQL query? (solution)

How to abide by all tabular array names inwards a database? (query)

How do y'all create backup of tabular array or re-create of tabular array using SQL? (answer)

How do y'all abide by all customers who bring never ordered? (solution)

Can y'all write pagination query for Oracle using row_number? (query)

How do y'all abide by Nth highest salary of an employee using the correlated query? (solution)

SQL Puzzles too Answers past times Joe Celko (read)

Share this post