How to remove duplicate records from a table? with out using distinct key?

KiruthikaSambandam · #1 (**permalink**) 08-06-2007, 06:23 AM

priyan · #2 (**permalink**) 08-06-2007, 08:04 AM

Hi,

I have used UNION functions to get the distinct result from a table.

Here are the details...

Quote:

mysql> create table test(id integer, name varchar(10));
mysql> insert into test values (1, 'john');
mysql> insert into test values (1, 'john');
mysql> insert into test values (2, 'mike');
mysql> insert into test values (2, 'philip');
mysql> insert into test values (3, 'smith');
mysql> insert into test values (3, 'smith');
mysql> select * from test;
+------+--------+
| id | name |
+------+--------+
| 1 | john |
| 1 | john |
| 2 | mike |
| 2 | philip |
| 3 | smith |
| 3 | smith |
+------+--------+
6 rows in set (0.00 sec)

mysql> select a.id, a.name from test a
-> union
-> select b.id, b.name from test b;
+------+--------+
| id | name |
+------+--------+
| 1 | john |
| 2 | mike |
| 2 | philip |
| 3 | smith |
+------+--------+
4 rows in set (0.00 sec)

mysql> select distinct * from test;
+------+--------+
| id | name |
+------+--------+
| 1 | john |
| 2 | mike |
| 2 | philip |
| 3 | smith |
+------+--------+
4 rows in set (0.00 sec)

I hope it will work !!..

kumaresan · #3 (**permalink**) 08-06-2007, 08:10 AM

drop table if exists duplicate;
CREATE TABLE duplicate (
i int,
name
varchar(20)
);
insert into duplicate values(1,'kumar');
insert into duplicate values(1,'kumar');
insert into duplicate values(2,'ram');
insert into duplicate values(2,'ram');
insert into duplicate values(3,'arjun');
insert into duplicate values(3,'arjun');

SELECT * FROM duplicate;

i name
----------------
1 kumar
1 kumar
2 ram
2 ram
3 arjun
3 arjun

to remove the duplicate rows
-------------------------------
alter ignore table duplicate add unique index(i,name);

SELECT * FROM duplicate;

i name
-----------------
1 kumar
2 ram
3 arjun

S.Vinothkumar · #4 (**permalink**) 08-06-2007, 08:27 AM

You can see answer here step by step...

prasath · #5 (**permalink**) 08-06-2007, 08:38 AM

Hi,
The below should display the distinct values

SELECT i,name FROM(select i,name,count(*) from duplicate
group by i,name having count(*)>1)t

Thanks,
Prasath.K

priyan · #6 (**permalink**) 08-06-2007, 08:55 AM

Hi Prasath,

You need to modify your query little bit. If i inserted another record into the duplicate table,

Quote:

insert into duplicate values(4,'raman');

select * from duplicate;
------------------
i name
------------------
1 kumar
1 kumar
2 ram
2 ram
3 arjun
3 arjun
4 raman
------------------

SELECT i,name FROM(select i,name,count(*) from duplicate
group by i,name having count(*)>1)t;

------------------
i name
------------------
1 kumar
2 ram
3 arjun
------------------
-- It's going to be wrong result, now modify the above query as given below.

SELECT i,name FROM(select i,name,count(*) from duplicate
group by i,name having count(*)>=1)t;
------------------
i name
------------------
1 kumar
2 ram
3 arjun
4 raman
------------------

prasath · #7 (**permalink**) 08-06-2007, 10:16 PM

Hi,
We concentrate tedious query,but GROUP BY simply do to remove the
duplicate records.

SELECT i,name from duplicate group by i,name

It is working as fine..........

Thanks,
Prasath.K

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.discussweb.com/database-support/3153-how-remove-duplicate-records-table-out-using-distinct-key.html
Posted By	For	Type	Date
DiscussWeb IT Community - Fusing	This thread	Refback	08-06-2007 10:37 PM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete records from multiple table at a time	Falcon	Database Support	7	04-07-2008 01:12 AM
How to get distinct records from datatable	oxygen	C# Programming	1	03-05-2008 02:47 AM
Duplicate Content	vadivelanvaidyanathan	Search Engine Optimization	11	02-27-2008 11:10 AM
What is the difference between DELETE TABLE and TRUNCATE TABLE commands in SQL Server	oxygen	Database Support	6	11-23-2007 06:17 AM
Duplicate elements from an array	vigneshgets	C and C++ Programming	1	09-06-2007 08:04 AM