Here is my sample table:
| idmain | idtime | idperson1 | idperson2 |
|---|---|---|---|
| 141 | 20220106 | 510 | 384 |
| 221 | 20220107 | 300 | 184 |
| 221 | 20220107 | 301 | 184 |
| 465 | 20220108 | 300 | 184 |
| 525 | 20220109 | 111 | 123 |
| 525 | 20220109 | 112 | 123 |
| 525 | 20220109 | 113 | 123 |
Duplicated records only differ by idperson1. So I require to remove these records preserving only the record with the max value of idperson1. So my final table should be:
| idmain | idtime | idperson1 | idperson2 |
|---|---|---|---|
| 141 | 20220106 | 510 | 384 |
| 221 | 20220107 | 301 | 184 |
| 465 | 20220108 | 300 | 184 |
| 525 | 20220109 | 113 | 123 |
CodePudding user response：
first you can use subquery to obtain max value of idperson1. then use this condition like this:
select a.* from fact1 a
where idperson1=(select max(b.idperson1) from fact1 b where a.idtime=b.idtime and a.idperson2=b.idperson2);