Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete duplicate rows and keep one row

This the name of my table Result_Simul

This is the value

       Pk   FkIdResult   FkIdSimul
       1        43         1244
       2        43         1244
       3        52         1244
       4        52         1244

How to keep just keep rows Pk=1 and Pk=3 and delete Pk=2 and Pk=4

Thanks for helping me.

Im not really good in Tsql

Frank

like image 318
FrankSharp Avatar asked Mar 23 '12 19:03

FrankSharp


People also ask

Does remove duplicates keep the first row?

➤ From the Remove Rows option under the Home tab, click on Remove Duplicates. Step 4: ➤ The duplicate rows will be removed keeping the first rows.


1 Answers

You can use row_number to give each duplicate an ascending number, and then delete the 2nd and higher duplicates:

delete  tbl
from    (
        select  row_number() over (partition by FkIdResult, FkIdSimul 
                                  order by Pk desc) as rn
        ,       *
        from    YourTable
        ) tbl
where   rn > 1

Working example at SE Data.

like image 119
Andomar Avatar answered Oct 08 '22 01:10

Andomar