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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

HuaMin ChenProblem resolverCommented:
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

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
Sha1395Author Commented:
Thanks HuaMinChen, after modified the query based on your suggestion still populate the duplicate records(same record count)
0
HuaMin ChenProblem resolverCommented:
Do you mean the same record appears more than once? Can you show the output data?
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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 ChenProblem resolverCommented:
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
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.