SELECT TOP 5 PERCENT Returns 75%

[perfectchaoss]

SELECT TOP 5 PERCENT dbo_FormReportSS.UPC, dbo_FormReportSS.ItemDesc, dbo_FormReportSS.WhseNum, dbo_FormReportSS.PriceOnly, dbo_FormReportSS.CaseCost, dbo_FormReportSS.UnitsCase, dbo_FormReportSS.UnitCost, dbo_FormReportSS.[GM%], Sum(dbo_FormReportSS.LstWght) AS SumOfLstWght, Sum(dbo_FormReportSS.LstMvmtCt) AS SumOfLstMvmtCt, Sum(dbo_FormReportSS.Sales) AS SumOfSales, dbo_FormReportSS.Brand, dbo_FormReportSS.CommCode, dbo_FormReportSS.DSDVendor

     FROM dbo_FormReportSS

WHERE (((dbo_FormReportSS.aisleloc) Like "*" & [Forms]![DeptMinorSS]![cboAisle] & "*") AND ((dbo_FormReportSS.Date)>=[Forms]![DeptMinorSS]![txtStartDate] And (dbo_FormReportSS.Date)<=[Forms]![DeptMinorSS]![txtEndDate]) AND ((dbo_FormReportSS.DeptNum)=[Forms]![DeptMinorSS]![cboDepartment]) AND ((dbo_FormReportSS.CommCode) Like [Forms]![DeptMinorSS]![cboCommCode]))

GROUP BY dbo_FormReportSS.UPC, dbo_FormReportSS.ItemDesc, dbo_FormReportSS.WhseNum, dbo_FormReportSS.PriceOnly, dbo_FormReportSS.CaseCost, dbo_FormReportSS.UnitsCase, dbo_FormReportSS.UnitCost, dbo_FormReportSS.[GM%], dbo_FormReportSS.Brand, dbo_FormReportSS.CommCode, dbo_FormReportSS.DSDVendor

HAVING (((dbo_FormReportSS.UPC) Like "*" & [Forms]![DeptMinorSS]![txtUPC] & "*") AND ((dbo_FormReportSS.DSDVendor) Like "*" & [Forms]![DeptMinorSS]![cboVendor] & "*"))
ORDER BY Sum(dbo_FormReportSS.Sales);

Rows returned when code is ran = 1308
Rows returned when Percent is changed to 100 = 1765

1308/1765 = 74%

Another note when i run this exact same code except DESCENDING (DESC) it returns the correct number of row's to be 5% of the total rowcount (89). 89/1765 = 5%

My logic is telling me that sorting the Query should not effect how many rows are returned, does anyone have any ideas why this is happening. Ive tried changing from ASC to DESC on simple queries with a SELECT TOP 5 PERCENT and it works exactly how it should.

Thanks,
Joe

 

[perfectchaoss]

Whoops please disregard this post, the reason was that the order by criteria, was the same value for 74% of the items. sorry wasn't thinking.

 

[Golom]

Thank you for posting your solution. I've been messing with this for some time trying to figure out what was wrong.

From your explanation it sounds like "TOP x PERCENT" returns all ties. That's different than just "TOP x" which returns the specified number of records with only some of the ties if the cutoff happens in the middle of a group of ties.

 

[JonFer]

This doesn't affect the "TOP xx" issue but you should change your HAVING clause to a WHERE clause. HAVING criteria should only be for aggregate functions. WHERE criteria are for the initial selection of records.