Are your smart devices spying on you? Make better purchasing choices and find products that respect your privacy at Unwanted.cloud
Latest posts from my personal blog
Stanislav Khromov
So you have a column that is not UNIQUE, but you’d still like to check if there is duplicate values in it.
If your column name is my_column in table my_table, the query is:
SELECT my_column, COUNT(*) as count
FROM my_table
GROUP BY my_column
HAVING COUNT(*) > 1
ORDER BY count DESC
This will return all records that have duplicate my_column content, as well as how many times this content occurs in the database.
Full-stack impostor syndrome sufferer & Software Engineer at Schibsted Media Group
Max Mustermann
Thanks for sharing this query. But how to show not only the related column and the could, but list the columns that are dupe? For example, the table has 2 columns ‘path’ and ‘folder’. Your query would list either ‘path’ or ‘folder’ and the count. But how can it list CONCAT(path,folder) to list the different paths?
Stanislav Khromov
Hey Max,
Try this:
SELECT CONCAT(path,folder), COUNT(*) FROM your_table GROUP BY path,folder;
That should give you the unique combinations. You can find some relevant code here:
http://stackoverflow.com/a/2421441/2572827
Max Mustermann
Tested and found this query to not find dupes anymore. I tried to fix it by adding the 'HAVING COUNT(*) > 1' contition with no luck
Stanislav Khromov
This works fine for me to detect entries with at least one duplicate:
SELECT CONCAT(path,folder), COUNT(*) as count FROM your_table GROUP BY path,folder HAVING COUNT(*) > 1;
Vijay Kumar Kanta
This worked flawlessly. Do not forget to use the same column for *SELECT*, *HAVING* and *COUNT* keyword/clauses. That was the mistake I was doing when the query wasn’t working. Not anymore.
Max Mustermann
I suppose the reason within the fact that this query compares the full path rather than the directory only. I aim to find dupe folders no matter which path is prepended and get the full path + folder listed.
Is that possible?
Stanislav Khromov
The query I wrote does compare the full path. It’s not really clear to me what you are after, you would have to explain better, give an example and preferably also the schema you use.
Max Mustermann
It’s not really clear to me what you are after
Ok, in short:
1 Table, 3 Columns (id, path, folder)
I am looking to find duplicates in the ‘folder’ column – duplicate folder names ONLY and want the output to show the full path to the duplicate folder (concate path + folder). There cannot be any duplicates when concatenating path and folder as this would always be the same folder which is why your 2nd query doesn’t find any duplciates in my table. But there can be duplicate folders at different paths. So the query must search for duplicates in the folder column and list both the path and folder may it be concatenated or separate so i can decide which folder to delete.
Adopting your initial query to my case this query lists duplicate folders and their duplicate count.
Now clearer?
Sean
Max you could use union to get the folder and path
SELECT folder, COUNT(*) as count FROM your_table GROUP BY folder
union
SELECT * FROM your_table GROUP BY folder
This should allow you to get the count from folder but also the path.
Pelle
I get the error 2001: The column ‘COUNT’ cannot be found in the table ‘…’ any ideas?
vijay
i need script , i want to open website on unique ip , if duplicate ip found , site will not open or redirect. plz help
Steffen
Dhanabalan S
joffer