Find MySql duplicated rows

How do i find the duplicate rows from a table?
I don't want to find duplicate entries for a column.. i have 30 columns in the table and i want to find duplicates rows that have exactelly same data for all columns

thanks

 

Use a union or sub-select in the from.

select * from table1 where NOT in (select * from table2) (not tested but it should be something like this, hehe

 

SELECT * FROM `Table` WHERE `Key` != `Key` AND `Column1` = `Column1` AND `Column2` = `Column2` AND `Column3` = `Column3` etc

 

That wouldn't work would it? That would be comparing each row to itself, wouldn't it???

I would just use a self join.

SELECT table1.c1, table1.c2, table1.c3, table2.c1, table2.c2, table2.c3
FROM table1
SELF JOIN ON table2.c1 = table1.c1 AND table2.c2 = table1.c2 AND table2.c3 = table1.c3;

The first column set will be all of the data from the table.

The 2nd column set should only be the duplicate content.

 

Nope, compares every row to every other row.

 

When it comes down to this i go to here http://www.phpfreaks.com/ and scratch my head untill I figure it out.

 

Scrap that - needed to do the same today and it didn't work . Could have sworn I did exactly that 2 weeks ago though

 

After some Gatorade style phpfreaks and headscratching I used

SELECT *
FROM `Table1` AS T1, `Table1` AS T2
WHERE T1.`Key` != T2.`Key`
AND T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2
ORDER BY T2.Col1

Code (markup):

etc