1

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;
  • 1
    Why does the expected 00 output match the sample data row for 01? – Michael Kutz May 2 '18 at 11:37
3

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
  • 1
    Hi Michael, I wonder if you would consider adding a MATCH_RECOGNIZE answer to the canonical version of this question, or editing perhaps editing the accepted answer there which details the idiomatic Oracle method before 12c, as it would be nice to have a single canonical answer too? – Jack Douglas May 5 '18 at 9:30
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.