Delete from GROUP BY with multiple GROUP columns I have a simple table consisting of id, channel_id, value and timestamp:
CREATE TABLE `data` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`channel_id` int(11) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL,
`value` float NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_ADF3F36372F5A1AA` (`channel_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2126679551

The combination of channel_id,timestamp MUST be unique but the table has gotten corrupted. My ultimate goal is to add a unique index which fails due to the duplicates.
The duplicates are:
select channel_id,timestamp,count(*) cnt 
from data 
group by channel_id,timestamp having cnt&gt;1

How can I delete the duplicates? I'm stuck since obviously I can't get the row id from the duplicates sub select?
UPDATE I could probably do
delete d1.* from data d1 join data d2 on d1.channel_id=d2.channel_id and d1.timestamp=d2.timestamp where d1.id != d2.id;

but since the table has almost 1 billion records a cross-product does not seem like a good idea?
DELETE t1
FROM data t1
JOIN data t2 USING (channel_id,timestamp)
WHERE t1.id &gt; t2.id