表名：表1列：（id，ref\u id，timestamp）

|**id**|**ref_id**|  **timestamp**     |                              
| 1      | 111        | 2020-04-27 02:59:45    |           
| 2      | 111        | 2020-04-28 02:59:45    |            
| 3      | 111        | 2020-04-26 02:59:45    |

我想实现一些

DELETE 
FROM   (
           SELECT *,
                  ROW_NUMBER() OVER(PARTITION BY red_id ORDER BY TIMESTAMP DESC) AS rn
           FROM   table_one
       ). 
       s
WHERE  r.rn != 1;

执行上述删除查询后的表1应该如下所示

|**id**|**ref_id**|  **timestamp**                                             
| 2      | 111        | 2020-04-28 02:59:45    |

我试图选择需要删除的行，但没有得到正确的行号。请在下面找到我的问题：

SET @row_number : = 0;   
SELECT *
FROM   (
           SELECT @row_number: = CASE 
                                      WHEN @ci = ref_id THEN @row_number + 1
                                      ELSE 1
                                 END  AS rn,
                  @ci: = ref_id          ref_id,
                  id,
                  TIMESTAMP
           FROM   table_one
           ORDER BY
                  TIMESTAMP              DESC

提前谢谢。