How to remove duplicate rows from SQL query

Here is my query:

SELECT TOP (100) PERCENT
       dbo.PMAPS.PMAP_Employee_NED_ID,
       dbo.Employees.First_Name,
       dbo.Employees.Last_Name,
       dbo.Employees.DeptID,
       dbo.Employees.Barg_Unit,
       dbo.Employees.Pay_Plan,  
       dbo.Employees.Grade, dbo.Employees.Last_Name + ', ' + dbo.Employees.First_Name AS Full_Name,
       dbo.FDA_Centers.Center_Acronym,
       MAX(dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed) AS PMAP_Establishment_Rating_Official_Signed,
       dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed,
       dbo.Employees.center_ID, 
       dbo.Employees.office_id,
       dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date,
       dbo.PMAPS.PMAP_MidYear_Estimated_completion_Reason,
       dbo.PMAPS.PMAP_Below_Level_Three, 
       dbo.PMAPS.PMAP_Contact_LER
FROM dbo.PMAPS
     INNER JOIN dbo.Employees ON dbo.PMAPS.PMAP_Employee_NED_ID = dbo.Employees.CapHR_ID
     INNER JOIN dbo.FDA_Centers ON dbo.Employees.center_ID = dbo.FDA_Centers.Center_ID
GROUP BY dbo.PMAPS.PMAP_Employee_NED_ID,
         dbo.PMAPS.PMAP_Year
         dbo.PMAPS.PMAP_IsActive
         dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date
         dbo.PMAPS.PMAP_Below_Level_Three
         dbo.Employees.center_ID
         dbo.Employees.isCommissionedCorps
         dbo.Employees.isExecutive
         dbo.Employees.Pay_Plan
         dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed
         dbo.FDA_Centers.Center_Acronym
         dbo.Employees.First_Name
         dbo.Employees.Last_Name
         dbo.Employees.DeptID
         dbo.Employees.Barg_Unit
         dbo.Employees.Pay_Plan
         dbo.Employees.Grade
         dbo.Employees.center_ID
         dbo.Employees.office_id
         dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed
         dbo.PMAPS.PMAP_MidYear_Estimated_completion_Reason
         dbo.PMAPS.PMAP_Contact_LER
HAVING  (dbo.PMAPS.PMAP_IsActive = 1)
        AND (dbo.Employees.isCommissionedCorps = 0)
        AND (dbo.Employees.isExecutive = 0)
        AND (NOT (dbo.Employees.Pay_Plan LIKE 'E%')
         AND NOT (dbo.Employees.Pay_Plan LIKE 'Z%')
         AND NOT (dbo.Employees.Pay_Plan LIKE 'S%')
          OR dbo.Employees.Pay_Plan IS NULL)
        AND (dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date IS NULL)
        AND (dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed IS NOT NULL)
        AND (dbo.Employees.center_ID = 14)
        AND (dbo.PMAPS.PMAP_Year = 2020)
        AND (dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed IS NULL)
ORDER BY dbo.PMAPS.PMAP_Employee_NED_ID;

Results:

enter image description here

Then data rows that are highlighted are the rows I need to display. I've tried MAX() (In query above), Distinct and nothing seems to work. Please advise. Thanks in advance.

1 answer

  • answered 2020-07-14 16:30 Gordon Linoff

    I think you just need to fix your GROUP BY:

    SELECT ID, First_Name, Last_Name, MAX(Date1) AS Date1, Date2
    FROM dbo.vw_getActiveEmployees ae LEFT JOIN
         dbo.vw_active_Employees_with_PMAPs ewp
         ON emp.CapHR_ID = ae.CapHR_ID LEFT JOIN
         dbo.vw_active_Employees_with_MidYear_PMAPs emp
         ON emp.CapHR_ID = ewp.CapHR_ID 
    GROUP BY ID,First_Name, Last_Name, Date2;
    

    Note that table aliases make the query easier to read. I also much prefer LEFT JOIN over RIGHT JOIN, because it says to keep everything in the first table (not the last one which hasn't been read yet).