如何实现3个字段相同时保留另一个字段值最大的那条记录

Question

有一个表
create table PeccancyRecord
(
  ID                VARCHAR2(50) not null,
  RECORDID          VARCHAR2(20) not null,
  DRIVINGLICENSENO  VARCHAR2(15),
  DRIVERNAME        VARCHAR2(15),
  CARMARK           VARCHAR2(15),
  CARTYPE           VARCHAR2(15),
  WORKNAME          VARCHAR2(100),
  ACCOUNTNO         VARCHAR2(15),
  CHECKINGPOSITION  VARCHAR2(50),
  CHECKINGFORM      VARCHAR2(50),
  CHECKINGDATE      DATE,
  TEAMNO            NUMBER(10),
  CHECKINGGROUPNO   VARCHAR2(15),
  INSPECTOR         VARCHAR2(15),
  OPERATIONKIND     VARCHAR2(50),
  CHECKINGCONDITION VARCHAR2(500),
  REVOKEFLAG        NUMBER(10),
  WARNFLAG          NUMBER(10),
  WRITEOFFFLAG      NUMBER(10),
  FORFEITSUM        NUMBER(10),
  KEAPLENGTH        NUMBER(10),
  MANAGESTATE       NUMBER(10),
  TRAINFLAG         NUMBER(10),
  MANAGEFLAG        NUMBER(10),
  RECORDFLAG        NUMBER(10),
  HANDLER           VARCHAR2(15),
  CHECKDATETIME     DATE,
  RESOLVEDATE       DATE,
  USERFEEDBACK      VARCHAR2(100),
  WORKFORFEIT       NUMBER(10),
  WORKRESOLVEIDEA   VARCHAR2(100),
  MODEL             VARCHAR2(50),
  CARCOLOR          VARCHAR2(15),
  ARTIFICIALPERSON  VARCHAR2(15),
  POSITION          VARCHAR2(50),
  ADDRESS           VARCHAR2(50),
  PHONENO           VARCHAR2(30),
  POSTALCODE        VARCHAR2(15),
  HISTORYRECORD     VARCHAR2(500),
  RECORDTIME        DATE,
  ILLEGALRECORD     VARCHAR2(50),
  ILLEGALKIND       VARCHAR2(30),
  PUNISHPERSON      NUMBER(10),
  PUNISHCOMPANY     NUMBER(10),
  DISTRICTNO        VARCHAR2(10),
  YSZTYPE           VARCHAR2(50),
  PUNISH            NUMBER(10),
  TIEDOCID          VARCHAR2(15),
  WHOLEFACTORYNAME  VARCHAR2(50),
  HOMEADDR          VARCHAR2(50),
  DELCASE           NUMBER(2),
  YSZNUM            VARCHAR2(15),
  SETDATETIME       DATE,
  KOUFENBZ          VARCHAR2(10),
  KOUFEN            NUMBER(10),
  TEACHFLAG         NUMBER(10),
  SUBMITDATE        DATE,
  SUBMITED          NUMBER(10) not null,
  EDITDATETIME      DATE,
  CUR_YEAR          VARCHAR2(8),
  CUR_MONTH         VARCHAR2(4)
)
要求是这样:
  如有多条“CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录，则仅保留ManageState数值最大的那条记录

Answer 1

select r.* from (
 select *,
row_number() over (partition by CarMark、CheckingDate、CheckingPositon order by ManageState desc nulls last) as rownumber
from PeccancyRecord
) r where r.rownumber = 1 ;

Answer 2

select r.* from (
 select *,
row_number() over (partition by CarMark,CheckingDate,CheckingPositon order by ManageState desc nulls last)as rownumber
from PeccancyRecord
) r where r.rownumber = 1 ;

Answer 3

delete from PeccancyRecord t1 where ManageState<>(
 select max(ManageState) from PeccancyRecord t2 where t1.CarMark=t2.CarMark and t1.CheckingDate=t2.CheckingDate
 and t1.CheckingPositon=t2.CheckingPositon)