How to remove duplicates SQL Server

[billy_111]

Hey everyone,

I was originally going to take care of this in the ASP code but i think its better to do it in the stored procedure instead. Basically i have the following statement:

DECLARE @AgentID int = 515, @SearchStr varchar(max) = 'CHLI'

SELECT	DISTINCT Q.ID, 
		CASE 
		WHEN EXISTS 
			(
				SELECT Q1.SID   
				FROM Quote Q1  
				LEFT JOIN Transport T1 ON Q1.ID = T1.QuoteID  
				LEFT JOIN Route R1 ON T1.RouteID = R1.ID  
				INNER JOIN Lookup L1 ON Q1.TID = L1.ID   
				WHERE Q1.Code = Q.Code AND Q1.SID = 2500  
				AND Q1.Depart >= GETDATE()  
			    AND Q1.Code LIKE ISNULL('%' + NULLIF(@SearchStr,'') + '%','%')  
				AND Q1.ID NOT IN (SELECT QuoteID FROM TourAgent WHERE AgentID = @AgentID)  
				AND (L1.Name <> 'Pot' OR R1.Code = '')    
			)
			THEN Q.Code + '*' ELSE Q.Code 
		END AS Code
FROM	Quote Q
LEFT JOIN Transport T ON Q.ID = T.QuoteID
LEFT JOIN Route R ON T.RouteID = R.ID
INNER JOIN Lookup L ON Q.TID = L.ID
WHERE	Q.Depart >= GETDATE()
AND		Q.SID IN (2500, 2550, 2540)
AND		Q.Code LIKE ISNULL('%' + NULLIF(@SearchStr,'') + '%','%')
AND		Q.ID NOT IN (SELECT QuoteID FROM TourAgent WHERE AgentID = @AgentID)
AND		(L.Name <> 'Pot' OR R.Code = '')
GROUP BY Q.Code, Q.ID, Q.SID
ORDER BY Code

This returns the following:

91978 CHLIB0211*
91979 CHLIB0211*
91980 CHLIB0211*
91981 CHLIB0211*
91982 CHLIB0211*

Now depending on the @SearchStr, this could also return multuple different records so it could even be something like this:

91978 CHLIB0211*
91979 CHLIB0211*
91980 CHLIB0211*
91981 CHLIB0211*
91982 CHLIB0211*
91983 PEPPM0311*
88692 4RIA1111
88668 4RIA911
88669 4RIA911
88806 4RIV1111
88819 4RIV911
88725 4RIV911
88729 4RIV911

Now i know there is an ID but that can be ignored. So basically what i need to do is simply remove duplicate names regardless of the ID it has as that is controlled elsewhere.

Can i do this?

[r937]

if the id can be ignored, then just don't return it in the SELECT clause, and apply DISTINCT