Eliminate duplicate rows from query output

Question

I have a large SELECT query with multiple JOINS and WHERE clauses. Despite specifying DISTINCT (also have tried GROUP BY) - there are duplicate rows returned. I am assuming this is because the query selects several IDs from several tables. At any rate, I would like to know if there is a way to remove duplicate rows from a result set, based on a condition. I am looking to remove duplicates from results if x.ID appears more than once. The duplicate rows all appear grouped together with the same IDs.

Query:

SELECT      e.Employee_ID, ce.CC_ID as CCID, e.Manager_ID, e.First_Name, e.Last_Name,,e.Last_Login,   
            e.Date_Created AS Date_Created, e.Employee_Password AS Password,e.EmpLogin 
            ISNULL((SELECT TOP 1 1 FROM Gift g 
            JOIN Type t ON g.TypeID = t.TypeID AND t.Code = 'Reb' 
            WHERE g.Manager_ID = e.Manager_ID),0) RebGift,
            i.DateCreated as ImportDate
FROM        @EmployeeTemp ct
JOIN        dbo.Employee c ON ct.Employee_ID = e.Employee_ID 
INNER JOIN  dbo.Manager p ON e.Manager_ID = m.Manager_ID
LEFT JOIN   EmployeeImp i ON e.Employee_ID = i.Employee_ID AND i.Active = 1
INNER JOIN  CreditCard_Updates cc ON m.Manager_ID = ce.Manager_ID
LEFT JOIN Manager m2 ON m2.Manager_ID = ce.Modified_By 
WHERE ce.CCType ='R' AND m.isT4L = 1
AND CHARINDEX(e.first_name, Selected_Emp) > 0
AND ce.Processed_Flag = @isProcessed

The `DISTINCT` clause will return all unique rows of **what's being selected**. If you include the `DISTINCT` and some values are being duplicated, it's probably because other columns you are returning are unique (e.g. different, or distinct). Could you post your actual results of that query as well as the desired result? — brazilianldsjaguar, Jun 04 '14 at 22:57
@Vld. Could you be more specific? I did try using GROUP BY, but it kept asking for all the columns to be included in the GROUP BY clause — user3685738, Jun 05 '14 at 00:21
@brazilianldsjaguar Yes that is correct, the employee IDs are duplicated because the CC_ID is unique for each employeeID. I don't have access to the results set at present regrettably. Is there a way to specify only one or some of the results as DISTINCT, and others (such as CC_ID) as not unique? — user3685738, Jun 05 '14 at 00:24
Well, if CC_ID is unique a 'quick' solution would be to remove it from the select statement. Unfortunately, looking at the statement, I'm not sure what `ce.CC_ID` is referencing, as I can't find the `ce` alias anywhere (even though it's used a couple of times) ... or the `e` alias for that matter! :P — brazilianldsjaguar, Jun 05 '14 at 00:28
They were in the JOIN statements, but I doctored the query for here. The c is actually meant to be an e and the cc a ce. Unfortunately, I need the CC_ID, however the unique ones that are excluded due to duplicate rows can be looked up within the application (in the few cases that they will be excluded) — user3685738, Jun 05 '14 at 00:39

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

I don't have enough reputation to add a comment, so I'll just try to help you in an answer proper (even though this is more of a comment).

It seems like what you want to do is select distinctly on just one column.

Here are some answers which look like that:

SELECT DISTINCT on one column

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

edited May 23 '17 at 12:27

Community

1
1

answered Jun 05 '14 at 00:58

user38858

306
4
14

Eliminate duplicate rows from query output

1 Answers1