Select distinct id with max date according to department I have a table such as following

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Smith    2008-01-11 
01     Testing Purpose  Windsor  2008-02-01 
02     Test             Thorn    2008-01-05 
02     Testing          Baker    2008-03-01 
03     Testing only     Sykes    2008-01-20


I want the output like below, it display the latest date for each rev no. and other details

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Windsor  2008-02-01 
01     Testing          Baker    2008-03-01
02     Testing only     Sykes    2008-01-20


But when I run the sql, it display the value with the latest date only like below, I want it display the latest date for each rev no. and it based on department(session).

RevNo  RevContent  PIC    Created
-----  ----------  -----  ----------
01     Testing     Baker  2008-03-01


What I have tried:

SELECT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE
FROM CCS2_TBL_MASTERLIST a 
WHERE CCSEQUIPMENTDPMT = :DPMT 
AND CCSREVEFFECTIVEDATE = (SELECT MAX(CCSREVEFFECTIVEDATE) FROM CCS2_TBL_MASTERLIST GROUP BY CCSMASTERLISTREVNO HAVING CCSMASTERLISTREVNO =a.CCSMASTERLISTREVNO) 
ORDER BY CCSMASTERLISTREVNO DESC;
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