How To Discovery Duplicate Records Inward A Tabular Array On Database - Sql Tips
Wednesday, June 20, 2018
Add Comment
How to respect duplicate records inwards tabular array is a pop SQL interview enquiry which has been asked equally many times equally difference betwixt truncate together with delete inwards SQL or finding instant highest salary of employee. Both of these SQL queries are must know for whatever ane who is appearing on whatever programming interview where unopen to questions on database together with SQL are expected. In gild to find duplicate records inwards database table y'all demand to confirm Definition of duplicates, for instance inwards below contact tabular array which is suppose to shop name together with phone number of contact, a tape is considered to hold upwards duplicate if both mention together with telephone seat out is same but unique if either of them varies. Problem of duplicates inwards database arise when y'all don't conduct maintain a primary fundamental or unique key on database together with that's why its recommended to conduct maintain a fundamental column inwards table. Anyway its slow to respect duplicate records inwards tabular array past times using group past times clause of ANSI SQL. Group past times clause is used to grouping information based upon whatever column or a seat out of columns. Here inwards gild to locate duplicate records nosotros demand to utilisation group past times clause on both name together with phone equally shown inwards second SQL SELECT query example. You tin forcefulness out encounter inwards commencement query that it listed Ruby equally duplicate tape fifty-fifty though both Ruby conduct maintain dissimilar telephone seat out because nosotros exclusively performed grouping past times on name. Once y'all conduct maintain grouped information y'all tin forcefulness out filter out duplicates past times using having clause. Having clause is counter purpose of where clause for aggregation queries. Just retrieve to render temporary mention to count() information inwards gild to utilisation them inwards having clause.
SQL Query to respect duplicate records inwards a tabular array inwards MySQL
In this department nosotros volition encounter SQL query which tin forcefulness out hold upwards used to locate duplicate records inwards table. As explained inwards previous section, Definition of duplicate depends upon concern rules which must hold upwards used inwards grouping past times clause. In next query nosotros conduct maintain used SELECT query to pick out all records from Contacts table. Here James, Johnny, Harry together with Ron are duplicated 4 times. mysql> pick out * from Contacts;
+-------+----------+
| name | phone |
+-------+----------+
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| Ruby | 8965342 |
| Ruby | 6888342 |
+-------+----------+
18 rows inwards gear upwards (0.00 sec)
Following SELECT query volition only respect duplicates records based on name which mightiness non hold upwards right if 2 contact of same but dissimilar numbers are stored, equally inwards next result gear upwards Ruby is shown equally duplicate which is incorrect.
mysql> pick out name, count(name) from contacts grouping past times name;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 2 |
+-------+-------------+
5 rows inwards gear upwards (0.00 sec)
This is the right means of finding duplicate contacts at it facial expression both mention together with telephone number together with exclusively impress duplicate if both mention together with telephone is same.
mysql> pick out name, count(name) from contacts grouping past times name, phone;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 1 |
| Ruby | 1 |
+-------+-------------+
6 rows inwards gear upwards (0.00 sec)
having clause inwards SQL query volition filter duplicate records from non duplicate records. As inwards next query it impress all duplicate records together with how many times they are duplicated inwards table.
mysql> pick out name, count(name) equally times from contacts grouping past times name, telephone having times>1;
+-------+-------+
| name | times |
+-------+-------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
+-------+-------+
4 rows inwards gear upwards (0.00 sec)
That's all on how to respect duplicate records inwards table, These SQL queries volition function on all database similar MySQL, Oracle, SQL Server together with Sybase equally it exclusively uses ANSI SQL together with doesn't utilisation whatever database specific feature. Another interesting SQL query interview enquiry is "How to delete duplicate records from table" which nosotros volition encounter inwards unopen to other post.
0 Response to "How To Discovery Duplicate Records Inward A Tabular Array On Database - Sql Tips"
Post a Comment