how to remove duplicate rows

I have the following query to retrieve bunch of data but somehow it creates duplicate rows for certain records.I tried distinct but no use

What i am doing wrong here
SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, AssessmentEcosystemCreditID,dbo.tblAssessmentEcosystemCredit.AssessmentVersionID , 
(COALESCE(dbo.tblManagementZone.SiteValueCurrentScore,0) 
-COALESCE(dbo.tblManagementZone.SiteValueFutureScore,0)) AS LossinSiteValueScore, 
5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp, 
dbo.tblVegetationZone.EECID, 
CASE WHEN dbo.tblVegetationZone.EECID > 0 THEN 3.0 
ELSE 1.0 
END AS EECOffSetMultiplier 
FROM dbo.tblAssessmentEcosystemCredit 
INNER JOIN dbo.tblVegetationType 
ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID 
INNER JOIN dbo.tblManagementZone 
ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID 
INNER JOIN dbo.tblVegetationZone 
ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID 
INNER JOIN dbo.tblAssessmentVersion AV ON 
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID 
INNER JOIN tblAssessment TBA ON 
TBA.AssessmentID = AV.AssessmentID 
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= @AssessmentVersionID

Open in new window

Sha1395Asked:
Who is Participating?
 
HuaMin ChenSystem AnalystCommented:
Try
select distinct a.* from (SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, AssessmentEcosystemCreditID,dbo.tblAssessmentEcosystemCredit.AssessmentVersionID ,
(COALESCE(dbo.tblManagementZone.SiteValueCurrentScore,0)
-COALESCE(dbo.tblManagementZone.SiteValueFutureScore,0)) AS LossinSiteValueScore,
5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp,
dbo.tblVegetationZone.EECID,
CASE WHEN dbo.tblVegetationZone.EECID > 0 THEN 3.0
ELSE 1.0
END AS EECOffSetMultiplier
FROM dbo.tblAssessmentEcosystemCredit
INNER JOIN dbo.tblVegetationType
ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID
INNER JOIN dbo.tblManagementZone
ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID
INNER JOIN dbo.tblVegetationZone
ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID
INNER JOIN dbo.tblAssessmentVersion AV ON
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID
INNER JOIN tblAssessment TBA ON
TBA.AssessmentID = AV.AssessmentID
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= @AssessmentVersionID) a
0
 
Sha1395Author Commented:
Thanks HuaMinChen, after modified the query based on your suggestion still populate the duplicate records(same record count)
0
 
HuaMin ChenSystem AnalystCommented:
Do you mean the same record appears more than once? Can you show the output data?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Sha1395Author Commented:
Even i modified the query like below

select distinct a.* from(SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, AssessmentEcosystemCreditID,dbo.tblAssessmentEcosystemCredit.AssessmentVersionID , 

5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp 
FROM dbo.tblAssessmentEcosystemCredit 
INNER JOIN dbo.tblAssessmentVersion AV ON 
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID 
INNER JOIN tblAssessment TBA ON 
TBA.AssessmentID = AV.AssessmentID 
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= 2151)a

Open in new window

Book1.xlsx
0
 
Sha1395Author Commented:
huh found it, this probably helps me

select distinct ManagementZoneID from tblAssessmentEcosystemCredit where AssessmentVersionID=2151

Open in new window


will return only 14  records, thats the correct count, thats what i am trying to do. Any idea how to modify my main query
0
 
HuaMin ChenSystem AnalystCommented:
In your Excel, can you point out which rows are duplicated ones?
0
 
Sha1395Author Commented:
ManagementZoneID is the duplicate ones ,for example ManagementZoneID="14340" keep duplicate
0
 
HuaMin ChenSystem AnalystCommented:
But the so-called duplicated rows are having different values of

AssessmentEcosystemCreditID,AssessmentVersionID,...
0
 
Sha1395Author Commented:
Check the ManagementZOne ID "14340" all the values are same ,so it clearly shows its duplicate,if i delete the record based on ManangementZone Id, i will solve this issue
Book2.xlsx
0
 
HuaMin ChenSystem AnalystCommented:
if i delete the record based on ManangementZone Id, i will solve this issue

Please do the backup of the records and proceed to next if possible.
0
 
Sha1395Author Commented:
Do you suggest to delete to ? or still am i missing something ?
0
 
Sha1395Author Commented:
Sorry and what's the best approach to delete those duplicate records based on ManagementZoneID ?
0
 
HuaMin ChenSystem AnalystCommented:
Can you list out the records of this

SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, AssessmentEcosystemCreditID,dbo.tblAssessmentEcosystemCredit.AssessmentVersionID , 
(COALESCE(dbo.tblManagementZone.SiteValueCurrentScore,0) 
-COALESCE(dbo.tblManagementZone.SiteValueFutureScore,0)) AS LossinSiteValueScore, 
5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp, 
dbo.tblVegetationZone.EECID, 
CASE WHEN dbo.tblVegetationZone.EECID > 0 THEN 3.0 
ELSE 1.0 
END AS EECOffSetMultiplier 
FROM dbo.tblAssessmentEcosystemCredit 
INNER JOIN dbo.tblVegetationType 
ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID 
INNER JOIN dbo.tblManagementZone 
ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID 
INNER JOIN dbo.tblVegetationZone 
ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID 
INNER JOIN dbo.tblAssessmentVersion AV ON 
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID 
INNER JOIN tblAssessment TBA ON 
TBA.AssessmentID = AV.AssessmentID 
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= @AssessmentVersionID
order by 1

Open in new window


to one list again and show it to me?
0
 
Sha1395Author Commented:
I have enclosed the output for your view. Thanks again for your help
Book3.xlsx
0
 
HuaMin ChenSystem AnalystCommented:
In the following
23
do you mean the records are repeating? but how about the 2nd column is having different values?
0
 
Sha1395Author Commented:
second column in primary (column)id for that table its called "AssessmentEcoSystemCreditID"
0
 
HuaMin ChenSystem AnalystCommented:
Can you exclude 2nd column and use my original way (by my 1st reply above)?
0
 
Sha1395Author Commented:
i tried the following

Returns 492 record wich is wrong

select distinct a.* from(SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, dbo.tblAssessmentEcosystemCredit.AssessmentVersionID , 
(COALESCE(dbo.tblManagementZone.SiteValueCurrentScore,0) 
-COALESCE(dbo.tblManagementZone.SiteValueFutureScore,0)) AS LossinSiteValueScore, 
5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp
FROM dbo.tblAssessmentEcosystemCredit 
INNER JOIN dbo.tblVegetationType 
ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID 
INNER JOIN dbo.tblManagementZone 
ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID 
INNER JOIN dbo.tblVegetationZone 
ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID 
INNER JOIN dbo.tblAssessmentVersion AV ON 
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID 
INNER JOIN tblAssessment TBA ON 
TBA.AssessmentID = AV.AssessmentID 
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= 2151)a

Open in new window


I tried the following query return 14 Record which is correct

Select distinct a.* from (SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, dbo.tblAssessmentEcosystemCredit.AssessmentVersionID  from tblAssessmentEcosystemCredit where AssessmentVersionID =2151)a

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
I told you 2nd column is the reason
0
 
Sha1395Author Commented:
even i removed the second column from the main query not returning the correct records
0
 
Sha1395Author Commented:
So do you think remove the duplicates from the table is a wise idea like below

delete from tblAssessmentEcosystemCredit where AssessmentEcosystemCreditID not in(
select min (AssessmentEcosystemCreditID) from tblAssessmentEcosystemCredit where AssessmentVersionID=2151 group by ManagementZoneID)
and AssessmentVersionID=2151 

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
Yes, but please do backup to the tables and ensure you can restore them back if needed!
0
 
Sha1395Author Commented:
Thanks a lot for your help and advice HuaMinChen.
0
 
Sha1395Author Commented:
I just used  delete query to remove the duplicates.
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.