How to remove duplicate rows from SQL query

Question

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:

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.

if you group by date1, it will separate out that data, and not aggregate even with a max. only ever group by fields you are not aggregating. — Jeremy, Jul 14 '20 at 16:26
Unrelated note, but a `TOP` with an `ORDER BY` always infers a design flaw. Also, referencing columns with 3 part naming is due to the deprecated and removed from SQL Server and should be avoided. I also **strongly** suggest using aliases. A clause like `dbo.vw_active_Employees_with_MidYear_PMAPs.CapHR_ID = dbo.vw_active_Employees_with_PMAPs.CapHR_ID` is very difficult to read quickly. — Thom A, Jul 14 '20 at 16:27
As for your qusetion, there are no "duplicate" rows, they are all different. But your aggregating **and** grouping on `Date1`, which makes toe aggregation pointless when you are using `MAX`. — Thom A, Jul 14 '20 at 16:27
But I can't NOT have date1 in the group by correct? Removing Date1 from the GroupBy throws an error that 'Date1 is not included in the group by'... — David Jacobson, Jul 14 '20 at 16:28
*"But I can't NOT have date1 in the group by correct? "* Why not? For a query that contains a `GROUP BY`, a column must be included in the `GROUP BY` **or** contained in an aggregate function. `Date1` is contained in an aggregate function; `MAX`. — Thom A, Jul 14 '20 at 16:28
@DavidJacobson try my suggestions. you only get that error for a non-aggregated field in the query. — Jeremy, Jul 14 '20 at 16:29
Column 'Date1' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. — David Jacobson, Jul 14 '20 at 16:29
WHERE filters non-aggregated fields. HAVING filters aggregated fields, so ensure that the field you are filtering is in the right type of clause — Jeremy, Jul 14 '20 at 16:31
Sorry guys, was trying to simplify my query. I've updated the query to the actual query I'm using. Sorry again. — David Jacobson, Jul 14 '20 at 16:34
Forget you ever learned about "TOP (100) PERCENT" - it does nothing useful. Your desired results now just adds confusion since it does not match your updated query. — SMor, Jul 14 '20 at 17:00
The fact that you have such a large `GROUP BY` now suggests a design flaw. What is your actual goal here? And you *really need to consider aliases for your objects in the `FROM` and dropping 3 part naming for those columns; your SQL is very difficult to read as it is. — Thom A, Jul 14 '20 at 17:05
all of your "HAVING" clauses are really "WHERE" clauses. You'll say "It works!"...except when it doesn't and you get confused. In general, start with everything in a where clause, then use a HAVING clause only where absolutely necessary. https://stackoverflow.com/questions/287474/whats-the-difference-between-having-and-where — Jeremy, Jul 14 '20 at 17:05
This latest query completely invalidates Gordon's answer as well. The `LEFT JOIN`s that were in your query don't even exist anymore... With respect, you have really wasted a lot of the time people have put in to try to help you here, due to how far you've moved your goal posts. We're not even in the same pitch any more. — Thom A, Jul 14 '20 at 17:11
@larnu, my apologies if I have wasted your or anyone else's time. I was trying to condense my query and make it simpler to read, but having done that only confused people. My apologies. — David Jacobson, Jul 14 '20 at 19:30

score 1 · Answer 1 · answered Jul 14 '20 at 16:30

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).

How to remove duplicate rows from SQL query

1 Answers1