您可以使用row_number()over()和min()over()窗口函数的公共表表达式来实现此结果：
查询：

with cte as
(
  select Employee,PositionCode,Dates,row_number()over(partition by Employee order by dates desc)rn,
         min(dates)over(partition by Employee,POSITIONCODE)minDates from empTable
)
select Employee,PositionCode,minDates Dates from cte where rn=1

输出：
| 员工|位置代码|日期|
| --------------|--------------|--------------|
| DAVE|公司简介|2022年12月8日|
| 珍妮|公司简介|23年1月15日|
fiddle

在Oracle 12中，您可以使用MATCH_RECOGNIZE。这还将检测员工是否已从一个职位切换到另一个职位，然后返回到第一个职位，并将检索该最终职位的最新出现的最早日期：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY employee
  ORDER BY "DATE" DESC
  MEASURES
    FIRST(positioncode) AS positioncode,
    LAST("DATE") AS earliest_date
  PATTERN ( ^ same_position+ )
  DEFINE
    same_position AS FIRST(positioncode) = positioncode
)

其中，对于样本数据：

CREATE TABLE table_name (EMPLOYEE, POSITIONCODE, "DATE") AS
SELECT 'DAVE',  'SW001', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 'DAVE',  'SW001', DATE '2022-12-08' FROM DUAL UNION ALL
SELECT 'DAVE',  'WB566', DATE '2021-12-01' FROM DUAL UNION ALL
SELECT 'JENNY', 'XD234', DATE '2023-01-15' FROM DUAL UNION ALL
SELECT 'JENNY', 'MX124', DATE '2022-12-12' FROM DUAL UNION ALL
SELECT 'ALICE', 'AB123', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 'ALICE', 'DE456', DATE '2022-01-02' FROM DUAL UNION ALL
SELECT 'ALICE', 'AB123', DATE '2022-01-03' FROM DUAL UNION ALL
SELECT 'ALICE', 'AB123', DATE '2022-01-04' FROM DUAL;

输出：
| 员工|位置代码|最早日期|
| --------------|--------------|--------------|
| 爱丽丝|公司简介|2019 -01- 23 00：00：00|
| DAVE|公司简介|2019 -08 -12 00：00：00|
| 珍妮|公司简介|2019 -01-15 00：00：00|
在早期版本中，您可以使用ROW_NUMBER解析函数的多重比较：

SELECT employee,
       positioncode,
       "DATE"
FROM   (
  SELECT employee,
         positioncode,
         "DATE",
         ROW_NUMBER() OVER (PARTITION BY employee ORDER BY "DATE" ASC) AS dt_rn
  FROM   (
    SELECT t.*,
           ROW_NUMBER() OVER (PARTITION BY employee ORDER BY "DATE" DESC)
             AS rn,
           ROW_NUMBER() OVER (
             PARTITION BY employee, positioncode ORDER BY "DATE" DESC
           ) AS pc_rn
    FROM   table_name t
  )
  WHERE  rn = pc_rn
)
WHERE  dt_rn = 1;

fiddle