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

why Testing Purpose | Windsor | 2008-02-01 whit revno 00 .. in your desired result ? the revno 00 is not the latest date related version .. — ScaisEdge, May 02 '18 at 07:33
@KaushikNayak, I just checked the edit history and it doesn't look like I did change the data, only the formatting. — jmcilhinney, May 02 '18 at 07:54
@jmcilhinney : Ok, then it seems N.I.A : You should edit your expected o/p to answer scaisEdge's question. — Kaushik Nayak, May 02 '18 at 08:00

score 0 · Answer 1 · answered May 02 '18 at 07:19

Your subquery group by each department (CCSMASTERLISTREVNO) but the HAVING clause is wrong. Instead you should use compound columns in an IN clause. Something like this:

SELECT CCSMASTERLISTREVNO
       , CCSREVCONTENT
       , CCSPREPAREDREV
       , CCSREVEFFECTIVEDATE
FROM CCS2_TBL_MASTERLIST a 
WHERE CCSEQUIPMENTDPMT = :DPMT 
AND (CCSMASTERLISTREVNO, CCSREVEFFECTIVEDATE) in 
 (
     SELECT CCSMASTERLISTREVNO, MAX(CCSREVEFFECTIVEDATE) 
     FROM CCS2_TBL_MASTERLIST 
     GROUP BY CCSMASTERLISTREVNO )
  ) 
ORDER BY CCSMASTERLISTREVNO DESC

score 0 · Accepted Answer · answered May 02 '18 at 07:25

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;

score -1 · Answer 3 · edited May 02 '18 at 10:07

-1

I didn't really understand your question but maybe this can help you.

SELECT RevNo, RevContent, PIC, MAX(Created) as Created
FROM your_table_name 
GROUP BY RevNo
ORDER BY Created DESC

I think you need first Group your entries by the RevNo and then you sort by taking the Last Created of each group.

edited May 02 '18 at 10:07

answered May 02 '18 at 08:05

Mamadou SECK

11
2

That is an invalid use of `group by` – May 02 '18 at 10:07

Select distinct id with max date according to department

3 Answers3