Why do I need to do a DISTINCT

Question

Why do I need to do a DISTINCT

TriggerMe

SSChampion

Points: 11752
More actions
January 26, 2006 at 7:45 pm

#96460

Can someone explain why I need to do a DISTINCT in the second query? The first query uses Temporary Tables.
The second query uses Derived Tables. But why do I need to do the DISTINCT using Derived Tables as oppossed to the first query that uses Temporary Tables?
SELECT A.PersonID
FROM ##OriginalCount A INNER JOIN ##NewCount B ON A.PersonID = B.PersonID
WHERE A.Total > B.Total
ORDER BY 1 DESC
SELECT DISTINCT A.PersonID
FROM ##AvgCost A INNER JOIN (SELECT PersonID, COUNT(*) AS 'Total'
     FROM ##AvgCost
     GROUP BY PersonID) B ON A.PersonID = B.PersonID
   INNER JOIN (SELECT PersonID, COUNT(*) AS 'Total'
     FROM ##AvgCostA
     GROUP BY PersonID) C ON B.PersonID = C.PersonID
WHERE B.Total > C.Total
ORDER BY 1 DESC
Kindest Regards,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Because in second query you select from ##AvgCost and as I can conclude from the query for 1st derived table it contains more than 1 record per PersonId

Try this:

SELECT DISTINCT B.PersonID

FROM (SELECT PersonID, COUNT(*) AS 'Total'

FROM ##AvgCost

GROUP BY PersonID) B

INNER JOIN (SELECT PersonID, COUNT(*) AS 'Total'

FROM ##AvgCostA

GROUP BY PersonID) C ON B.PersonID = C.PersonID

WHERE B.Total > C.Total

_____________
Code for TallyGenerator

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Sergiy,

You led me to the answer. In the first query, the Temporary Tables have only 1 row per PersonID. That is why I need the DISTINCT in the 2nd query as the Derived Tables contain multiple PersonID's for the same PersonID.

Thanks.

Kindest Regards,