[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;

Open in new window


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

Thanks,
gdunn59
0
gdunn59
Asked:
gdunn59
  • 3
  • 2
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This is likely because you have exact duplicates.

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

mx
0
 
Dale FyeCommented:
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
 
gdunn59Author Commented:
I removed the Distinct, but still having the issue.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
 
gdunn59Author Commented:
So there is basically no way around this, correct?
0
 
Gustav BrockCIOCommented:
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
 
gdunn59Author Commented:
This worked perfect.

Thanks,

gdunn59
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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