abksharma abksharma - 1 year ago 69
MySQL Question

How to Convert Row_number with Common table expression query to Mysql

The following query contains SQL with

over a windowing function and a common table expression:

WITH numbered AS (
PARTITION BY master_id, Report_id
ORDER BY master_id, Report_id
) AS _dupe_num
FROM #tradePositionFilterInfo
DELETE FROM numbered
WHERE _dupe_num > 1

How can I convert this to work in MySQL?

Answer Source

Use mysql's multitable delete syntax:

delete a
from #tradePositionFilterInf a, #tradePositionFilterInf b
where a.master_id = b.master_id
and a.Report_id = b.Report_id
and a.id > b.id

The last line is the key. I chose the id column to break ties, assuming there will be such a column. If not, use timestamp or similar. If no time-based column, use any column that's different. If all columns are identical, you're hosed.