Search code examples
mysqlsql-server-2008database-migrationcommon-table-expressionrow-number

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?

Solution

  • 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.