Remove duplicates from MySQL table where 2 fields are the same

Question

So I have a table with fields ID (AI, primary key), ticker, priceDate, price.

I have a bunch or records that share the same priceDate and ticker. There should only be one record per ticker for any given priceDate.

How would I go about removing these duplicate records, given that priceDate and ticker are not unique fields?

A solution to this question has already been posted. http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql/5016434#5016434 — Linus Juhlin, May 10 '13 at 12:49

score 1 · Accepted Answer · answered May 10 '13 at 12:49

1

delete from your_table
where id not in 
(
  select * from 
  (
    select min(id)
    from your_table
    group by pricedate, ticker
  ) x
)

answered May 10 '13 at 12:49

juergen d

Many thanks, think it did the trick. Out of interest what does the `x` at the end do? – harryg May 10 '13 at 12:54
If you define a subquery you need to name it with an alias. I used `x` as alias name. – juergen d May 10 '13 at 13:07
BTW you need that sub query because MySQL won't allow you to delete from the same table you are selecting from. But with another subquery you can trick MySQL. – juergen d May 10 '13 at 13:09

1 Answers1