Getting all data and taking out duplicates of table

There's a table that has three fields: id, word, username

And it currently has d开发者_Go百科ata like this:

1 | run | thisguy
2 | run | thisguy
3 | go | thatgirl
4 | go | thatguy
5 | go | thatgirl

The ones I want is 1, 3, and 4. I don't want 2, and 5 because they're duplicates. How would I take them out?

1. Doing on the same table : Create a unique index on the required fields/

2. Doing in new table :

a) Create a temporary table by selecting unique data from original table.

b) Drop original table.

c) rename temporary table to original table's name.

 CREATE TABLE tempr SELECT id, type,name FROM myTable GROUP BY type,name;
 DROP TABLE myTable;
 ALTER TABLE tempr RENAME TO myTable ;

Also create unique index on type,name on this table to prevent future duplicates.

DELETE t2 FROM mytable AS t1
JOIN mytable AS t2 USING (word, username)
WHERE t1.id < t2.id;

You can use mysqls build in function distinct or GROUP BY

Distinct

Mysql_query("SELECT DISTINCT id, word, username FROM persons");

Group by

Mysql_query("SELECT * FROM persons GROUP BY word");

of course you can group by username or whatever you would like too.

Jonas

if you want to have only unique records in your table then do what DhruvPathak has said in his answer. But if you want to get the records through query then you can use distinct keyword. Your query will look like this

select id,distinct(word),username from $table

Have you tried using DISTINCT clause?