abksharma abksharma - 18 days ago 6
MySQL Question

How to Convert Row_number with Common table expression query to Mysql

The following query contains SQL with

ROW_NUMBER()
over a windowing function and a common table expression:

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


How can I convert this to work in MySQL?

Answer

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.

Comments