[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 482
• Last Modified:

# Top 10 Results Giving Me 12 - Not 10

I have the following code where I am trying to pull the Top 10, but it gives me 12 (instead of 10).

If I change the code to Top 9 or Top 5, that works, but the minute I change it to Top 10, it doesn't give me 10.

Here is the code:

``````SELECT DISTINCT TOP 10 [tblErrorDetails (for Report)].SectionDesc, Sum(IIf([Assoc]="Y","1","0")) AS Associate, Sum(IIf([Opers]="Y","1","0")) AS Department_, Sum(IIf([Impact]="Y","1","0")) AS Impact_
FROM [tblErrorDetails (for Report)]
GROUP BY [tblErrorDetails (for Report)].SectionDesc
ORDER BY Sum(IIf([Assoc]="Y","1","0")) DESC , Sum(IIf([Opers]="Y","1","0")) DESC , Sum(IIf([Impact]="Y","1","0")) DESC;
``````

Why does it work with 5 or 9, but not 10?

Thanks,
gdunn59
0
gdunn59
Asked:
• 3
• 2
• 2
• +1
1 Solution

Commented:
This is likely because you have exact duplicates.

Also, why are you using both DISTINCT and GROUP BY ?

mx
0

Commented:
That is because the Order By clause has more than 10 items with the same value as the 10th item in the list.  If your tenth item had 20 records with values of 0, 1, 1 for the three indicators in the Order By clause, and 9 values prior to that in the ordered list, then the results would contain 29 records.

The only way to ensure you get only 10 would be to add more fields to the OrderBy clause
0

Author Commented:
I removed the Distinct, but still having the issue.
0

Commented:
Well, yes ... because the real issue is what fyed and myself described ...

Look at your results - and you s/b able to see this ...

This is why the TOP function can be misleading ...

mx
0

Author Commented:
So there is basically no way around this, correct?
0

CIOCommented:
There is. Run your Query using distinct or Group by - then use this Query as the source in another Query (straight select) where you apply Top 10.

/gustav
0

Author Commented:
This worked perfect.

Thanks,

gdunn59
0

CIOCommented:
You are welcome!

/gustav
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.