返回最近的记录

Question

我有一个问题，为用户返回最新的SOP。在我的代码中，它返回所有记录，而不仅仅是最近的记录。在下面的示例表中，我只想为用户John返回第2行和第4行，因为这些是此SOP的最新版本，如本例中引用的最后两位数字所示。

示例数据集

ID ParentID Name  Reference   Title                  Unit  Dept Site Div Status
1  4        John  SOP/QA01/01 Document Control       CE    IS   CR   AR  Closed-Training Completed
2  4        John  SOP/QA01/02 Document Control       CE    IS   CR   AR  Opened
3  4        John  IS.01.06    System Administration  CE    IS   CR   AR  Closed-Cancelled
4  4        John  IS.01.07    System Administration  CE    IS   CR   AR  Pending Approval

SQL代码

 SELECT PR.ID AS RecordID,
          PR.Parent_ID,                                    
          PR.Responsible_Name AS Name,
          MAX(PR.Name) AS Reference,
          TT.S_Value AS Title,
          EU.S_Value AS Unit,
          ED.S_Value AS Dept,
          ES.S_Value As Site,
          EDV.S_Value AS Div,
          ST.NAME AS Status
     FROM PR
          INNER JOIN TW_V_Training_Title TT
             ON PR.ID = TT.PR_ID
             INNER JOIN PR_Status_Type ST
             ON PR.Status_Type = ST.ID
          INNER JOIN TW_V_Employee_Department ED
             ON PR.Parent_ID = ED.PR_ID
          INNER JOIN TW_V_Employee_Unit EU
             ON PR.Parent_ID = EU.PR_ID
           INNER JOIN TW_V_Employing_Site ES
          ON PR.Parent_ID = ES.PR_ID
          INNER JOIN TW_V_Employee_Division EDV
          ON PR.Parent_ID = EDV.PR_ID
          GROUP BY 
          PR.ID,
          PR.PARENT_ID,
          PR.Name,
          TT.S_Value,
          ST.NAME,
          EU.S_Value,
          ED.S_Value,
          ES.S_Value,
          EDV.S_Value,
          PR.NAME;

Answer 1

您在导致此问题的group by中使用了PR.NAME和ST.NAME。您需要以下查询：

SELECT PR.ID                 AS RECORDID,
       PR.PARENT_ID,
       PR.RESPONSIBLE_NAME   AS NAME,
       MAX(PR.NAME) AS REFERENCE,
       TT.S_VALUE            AS TITLE,
       EU.S_VALUE            AS UNIT,
       ED.S_VALUE            AS DEPT,
       ES.S_VALUE            AS SITE,
       EDV.S_VALUE           AS DIV,
       MAX(ST.NAME) KEEP(DENSE_RANK LAST ORDER BY PR.NAME) AS STATUS -- this is needed as it is different in each record
  FROM PR
 INNER JOIN TW_V_TRAINING_TITLE        TT
ON PR.ID = TT.PR_ID
 INNER JOIN PR_STATUS_TYPE             ST
ON PR.STATUS_TYPE = ST.ID
 INNER JOIN TW_V_EMPLOYEE_DEPARTMENT   ED
ON PR.PARENT_ID = ED.PR_ID
 INNER JOIN TW_V_EMPLOYEE_UNIT         EU
ON PR.PARENT_ID = EU.PR_ID
 INNER JOIN TW_V_EMPLOYING_SITE        ES
ON PR.PARENT_ID = ES.PR_ID
 INNER JOIN TW_V_EMPLOYEE_DIVISION     EDV
ON PR.PARENT_ID = EDV.PR_ID
 GROUP BY PR.ID,
          PR.PARENT_ID,
          --PR.Name, -- removed this
          TT.S_VALUE,
          --ST.NAME, -- removed this
          EU.S_VALUE,
          ED.S_VALUE,
          ES.S_VALUE,
          EDV.S_VALUE;
          --PR.NAME; -- removed this