CODEWITHSUNDEEP
sqloracle-database
NMS
NMS

Reputation: 35

Return most recent records

I am having an issue returning the most recent SOPs for users. In my code it is returning all records instead of just the most recent. In the example table below I only want to return rows 2 & 4 for user John as these are the most recent versions of this SOPs as indicated by the last two digits of the reference in this example.

Example dataset

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 Code

 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;

Upvotes: 0

Views: 54

Answers (1)

Popeye
Popeye

Reputation: 35900

You have used PR.NAME and ST.NAME in the group by that is causing this issue. You need the following query:

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

Upvotes: 1

Related Questions