Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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
Posted
Updated 1-May-18 21:27pm
v2

1 solution

It should be something like that:
SQL
SELECT A.RevNo, A.RevContent, A.PIC, A.Created
FROM YourTable A INNER JOIN (
    SELECT RevNo, MAX(Created) AS Created
    FROM YourTable
    GROUP BY RevNo) AS B ON A.RevNo = B.RevNo AND A.Created = B.Created


For further details, please see: Visual Representation of SQL Joins[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900