Duplicate record in SQL

Hi experts, i have a very long SQL statement, and this sql return a record, but i found that some of these record are duplicated, is there any way to retrieve proper records without any duplicate records.
---------------------------------------------
SELECT  
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, TRK.InitiatorName AS InitiatorUID,INI.DirCode,
INI.EngDir,INI.SecCode,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
INI.EngSec,dbo.CurrentUserName(TRK.InitiatorName) AS InitiatorName, TRK.RequestedDate AS RequestedDateString, CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate,TRK.Status, TRK.WorkflowPID, TRK.CurrentActorUID, SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode, dbo.CurrentUserName(TRK.CurrentActorUID) AS CurrentActorName, TRK.ApprovedDate,TRK.DeliveredDate, 'Out' as InOut,dbo.ActualKPI(TRK.ApprovedDate,TRK.DeliveredDate) as ActualKPI
FROM
tbl_ServiceTracking AS TRK
INNER JOIN
tblService AS SER ON SER.id = TRK.ServiceNo
INNER JOIN
PR1.dbo.GetUserDetailsE AS INI ON TRK.InitiatorName = INI.PERUserName  
LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE
CAST(TRK.RequestedDate AS DATETIME)>='5/9/2011' AND CAST(TRK.RequestedDate AS DATETIME)<='10/10/2011'
ORDER BY
ReferenceNo DESC
-----------------------------------------------

help me solve this.
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
Anthony PerkinsCommented:
>>i tried DISTINCT and its not working<<
Actually it works fine, it just does not do what you need.  As previously indicated all values in all columns must be the same for it to be considered a duplicate.  Your "duplicates" are just a few columns.  Try excluding all the other columns and DISTINCT "will work" just fine.  On the other hand, usually when someone uses DISTINCT it is usually a sign of desperation at an ill-conceived design...
0
 
Luan JubicaProject ManagerCommented:
Hello,
If the original table has them only once than somewhere your joins are duplicating the records (maybe the functions)
If they are twice in the original table than your problem is not the query but the table.

0
 
tigin44Commented:
simply you can get the distinct records during the select query by using the DISTINCT keyword...

SELECT  DISTINCT
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, TRK.InitiatorName AS InitiatorUID,INI.DirCode,
INI.EngDir,INI.SecCode,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
INI.EngSec,dbo.CurrentUserName(TRK.InitiatorName) AS InitiatorName, TRK.RequestedDate AS RequestedDateString, CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate,TRK.Status, TRK.WorkflowPID, TRK.CurrentActorUID, SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode, dbo.CurrentUserName(TRK.CurrentActorUID) AS CurrentActorName, TRK.ApprovedDate,TRK.DeliveredDate, 'Out' as InOut,dbo.ActualKPI(TRK.ApprovedDate,TRK.DeliveredDate) as ActualKPI
FROM
tbl_ServiceTracking AS TRK
INNER JOIN
tblService AS SER ON SER.id = TRK.ServiceNo
INNER JOIN
PR1.dbo.GetUserDetailsE AS INI ON TRK.InitiatorName = INI.PERUserName  
LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE
CAST(TRK.RequestedDate AS DATETIME)>='5/9/2011' AND CAST(TRK.RequestedDate AS DATETIME)<='10/10/2011'
ORDER BY
ReferenceNo DESC


but you should analyze your query for the source of the duplicate records and produce the solution to eliminate them.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tigin44Commented:
by the way the DISTINCT keyword elsiminates only the records that are having the same value in all columns...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may want to read this article to understand the issue, and solve it:
http://www.experts-exchange.com/A_3203.html
0
 
jvejskrabCommented:

You probably have some logical mistake in the query or you have some duplicates in the underlying tables
Try rewrite the query instead of using DISTINCT
Start the query with single table and add tables step by step, examining the result for duplicates
0
 
AZZA-KHAMEESAuthor Commented:
Thank you all for your replies, i tried DISTINCT and its not working, and yes there are a logical mistake in my query and i am trying to find it, and i will get back with the result
0
 
DaemonBarberCommented:
Try removing a join at a time.  For instance, remove the kept outer join on tb_task.  In all likelyhood, the outer join using I'd/status is returning multiple rows.

Naturally, without knowing your data model this is a guess.  But if you remove the join and it returns distinct records, then you can try and figure out what else to joi. O. To restrict the set.

0
 
DaemonBarberCommented:
Kept outer join should be left outer join.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.