Select distinct id with max date according to department

Question

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-11
 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

My query:

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

Answer 1

One option is to use row_number()

SELECT RevNo
    ,RevContent
    ,PIC
    ,Created
FROM (
    SELECT t.*
        ,row_number() OVER (
            PARTITION BY RevNo ORDER BY Created DESC
            ) AS rn
    FROM t
    )
WHERE rn = 1;

Another is to use the LAST aggregate function.

SELECT RevNo
    ,MAX(RevContent) KEEP ( DENSE_RANK LAST ORDER BY CREATED ) as RevContent
    ,MAX(PIC) KEEP ( DENSE_RANK LAST ORDER BY CREATED ) as PIC
    ,MAX(Created) as CREATED
FROM t
GROUP BY RevNo;