I wanted to display the duplicate values - anyway I got a workaround using HAVING. But I wanted the id also to be displayed alongside each duplicate entry.
The following query would return 50 rows - say there are 2 duplicates for each row (COUNT(`col1`) being 2 for each row) - but I was hopin for a solution to return 100 rows, id, col1.
SELECT `col1`, COUNT(`col1`)
GROUP BY `col1`
HAVING COUNT(`col1`) > 1
Thanks very much - this did solve my problem - this query on my laptop took 8 min to execute though ! Is there any way to optimize it for speed ? The table has 6000 records. Im sure my linux server host would be faster, but the number of records will grow over time.
My initial reason for all this was because I wanted to make col1 UNIQUE. Now that I got all duplicates and replaced them with different values, I did ALTER TABLE `tbl1` ADD UNIQUE (`col1`) ;
But this is after I changed duplicate entries, before that on what basis can I create an index ?