如何实现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数值最大的那条记录有会的 跟我联系也行 分一样给你 
qq 58021306msn [email protected]加时请注明

Answer 1

DELETE FROM PeccancyRecord a 
WHERE
    EXISTS(SELECT * 
           FROM 
               PeccancyRecord 
           WHERE 
               CarMark=a.CarMark 
               AND 
               CheckingDate=a.CheckingDate 
               AND 
               CheckingPositon=a.CheckingPositon
               AND
               ManageState>a.ManageState)

Answer 2

delete from PeccancyRecord
 where id in (select id
                from (select id,
                             dense_rank() over(partition by CarMark, CheckingDate, CheckingPositon order by v.ManageState, v.id desc) r
                        from PeccancyRecord v) a
               where a.r > 1)这条语句就可以了，只要ID是你的主健

Answer 3

delete from PeccancyRecord
where rowid in (select rid
from (select rowid rid,
dense_rank() over(partition by CarMark, CheckingDate, CheckingPositon order by v.ManageState desc) r
from PeccancyRecord v) a
where a.r > 1)