根据部门选择具有最大日期的不同ID [英] Select distinct id with max date according to department

问题描述

我有一张如下表格

 RevNo | RevContent | PIC |创建
 --------------------------------------------- ---------- 
 00 |测试目的|史密斯| 2008-01-11 
 01 |测试目的|温莎| 2008-02-01 
 02 |测试|索恩| 2008-01-05 
 02 |测试|贝克| 2008-03-01 
 03 |仅测试|赛克斯| 2008-01-20 

我想要输出如下，它显示每个转速的最新日期。和其他细节

 RevNo | RevContent | PIC |创建
 --------------------------------------------- ------- 
 00 |测试目的|温莎| 2008-02-01 
 01 |测试|贝克| 2008-03-01 
 02 |仅测试|赛克斯| 2008-01-20 

但是当我运行sql时，它只显示最新日期的值，如下所示，我想要它显示每个转速的最新日期。它基于部门（会话）。

 RevNo | RevContent | PIC |创建
 --------------------------------------------- ------- 
 01 |测试|贝克| 2008-03-01 

我的尝试：

  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  

解决方案

它应该是这样的：

  SELECT  A.RevNo，A.RevContent，A.PIC，A.Created 
  FROM  YourTable A  INNER   JOIN （
  SELECT  RevNo，MAX（已创建）  AS 已创建
  FROM  YourTable 
  GROUP   BY  RevNo） AS  B  ON  A.RevNo = B.RevNo  AND  A.Created = B.Created 

有关详细信息，请参阅： SQL联接的可视化表示 [ ^ ]

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

解决方案

It should be something like that:

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[^]

这篇关于根据部门选择具有最大日期的不同ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！