Select distinct id with max date according to department
12c+

Oracle 12c introduces the MATCH_RECOGNIZE syntax.

It may appear cumbersome at first, but I find that it becomes drastically easier to enhance and maintain as you add information that isn't from last row only.

Code:

with data(revno,revcontent,PIC,created) as (
select '00',     'Testing Purpose',  'Smith',    date '2008-01-11' from dual union all
select '01',     'Testing Purpose',  'Windsor',  date '2008-02-01' from dual union all
select '02',     'Test',             'Thorn',    date '2008-01-05' from dual union all
select '02',     'Testing',          'Baker',    date '2008-03-01' from dual union all
select '03',     'Testing only',     'Sykes',    date '2008-01-20' from dual
)
select *
from data
  MATCH_RECOGNIZE (
    partition by revno
    order by created
    MEASURES
        last(anyrow.revcontent) as revcontent,
        last(anyrow.pic) as pic,
        last(anyrow.created) as created,
        first(anyrow.revcontent) as first_revcontent,
        first(anyrow.pic) as first_pic,
        first(anyrow.created) as first_created
    one row per match
    PATTERN (ANYROW*)
    define
      ANYROW AS 1=1
  )


Table Results

REVNO REVCONTENT      PIC     CREATED   FIRST_REVCONTENT FIRST_PIC FIRST_CREATED
----- --------------- ------- --------- ---------------- --------- -------------
00    Testing Purpose Smith   11-JAN-08 Testing Purpose  Smith     11-JAN-08
01    Testing Purpose Windsor 01-FEB-08 Testing Purpose  Windsor   01-FEB-08
02    Testing         Baker   01-MAR-08 Test             Thorn     05-JAN-08
03    Testing only    Sykes   20-JAN-08 Testing only     Sykes     20-JAN-08