distinct records in UNION all

Hi experts
i have a very long sql statetement with UNION ALL command, the problem that i am getting duplicate records, how can i retrieve only unique records. any suggestion
SELECT			TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, 
							SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, 
							trk.InitiatorName AS InitiatorUID, PER.DirCode,
							PER.SecCode, PER.EngSec, dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, 
							trk.RequestedDate AS RequestedDateString, 
							CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, 
							trk.WorkflowPID, trk.CurrentActorUID, dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, 
							trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut, PER.EngDir
			FROM			PR1.dbo.GetUserDetailsE AS PER 
			INNER JOIN		PR1.dbo.GetUserDetailsE AS PER2 ON PER.DirCode = PER2.DirCode 
			INNER JOIN		dbo.tbl_ServiceTracking AS TRK ON PER2.PERUserName = TRK.InitiatorName 
			INNER JOIN		dbo.tblService AS SER ON TRK.ServiceNo = SER.id   
			LEFT OUTER JOIN AdobeLCES2.dbo.tb_task AS TASK 
			ON				TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
			WHERE			(PER.PERUserName = 'MFFIMRF') 
			AND				CAST(TRK.RequestedDate AS DATETIME)>='9/1/2013' 
			AND				CAST(TRK.RequestedDate AS DATETIME)<='9/30/2013'
			


UNION ALL
			SELECT			TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, 
							SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, 
							trk.InitiatorName AS InitiatorUID, PER.DirCode,
							PER.SecCode, PER.EngSec, dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, 
							trk.RequestedDate AS RequestedDateString, 
							CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, 
							trk.WorkflowPID, trk.CurrentActorUID, dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, 
							trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut,
							PER.EngDir
			FROM			PR1.dbo.GetUserDetailsE AS PER 
			INNER JOIN		WorkFlow.dbo.tblService AS SER ON SER.DirCode = PER.DirCode 
			INNER JOIN		WorkFlow.dbo.tbl_ServiceTracking AS trk ON trk.ServiceNo = SER.id   
			LEFT OUTER JOIN AdobeLCES2.dbo.tb_task AS TASK 
			ON				TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
			WHERE           (PER.PERUserName = 'MFFIMRF') 
			AND				CAST(TRK.RequestedDate AS DATETIME)>='9/1/2013' 
			AND				CAST(TRK.RequestedDate AS DATETIME)<='9/30/2013'
			ORDER BY		RequestedDate  DESC

Open in new window

LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
The Union All statement includes records from all sources and will give you duplicates.  The Union statement (without the All) will not give you duplicates.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AZZA-KHAMEESAuthor Commented:
thank you for the reply, its working now with union, but i faced another problem with ntext field i am getting this error

The ntext data type cannot be selected as DISTINCT because it is not comparable.

is there any solution instead of changing field type?
0
AZZA-KHAMEESAuthor Commented:
ohh i solved it, i convert the field to nvarchar using

cast(TRK.Details as nvarchar(MAX)) as Details

Open in new window


thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.