I have a table item having just one column name:-
name
-----
toys
shirt
mobile
Shirt
speaker
Toys
....
....
I am trying to delete duplicate rows except one duplicated record with the help of below query
DELETE FROM
(
SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,
@num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM
item ORDER BY `name`) AS result1
) AS result
WHERE rownumber >1
The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.
I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.