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

score 3 · Answer 1 · answered May 02 '18 at 17:58

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

score 2 · Answer 2 · edited May 02 '18 at 08:49

2

SELECT * FROM (
   SELECT
     CCSMASTERLISTREVNO,
     CCSREVCONTENT,
     CCSPREPAREDREV,
     CCSREVEFFECTIVEDATE,
   RANK() OVER (PARTITION BY CCSMASTERLISTREVNO
                ORDER BY salary DESC) AS Rank
   FROM CCS2_TBL_MASTERLIST) a
WHERE Rank = 1

Try using RANK() to get the desired output.

edited May 02 '18 at 08:49

ypercubeᵀᴹ

99,450
13
217
306

answered May 02 '18 at 08:10

Edgar Allan Bayron

1,350
4
16
32

Select distinct id with max date according to department

2 Answers2

12c+

Linked