SELECT TOP 10 Query returns 12 records

[Paul H]

Here's my SQL

SELECT TOP 10 tbl_ChartData.MACHINE, tbl_ChartData.Shift_Order, tbl_ChartData.DT_Reason_Code, tbl_ChartData.DT_Reason, tbl_ChartData.Shift AS Shift1, Sum(tbl_ChartData.[Count]) AS Data1
FROM tbl_ChartData
WHERE (((tbl_ChartData.ProductionDate)>=DateValue([Forms]![frm_Chart]![txt_FromDate])-1+#12/30/1899 23:0:0# And (tbl_ChartData.ProductionDate)<=DateValue([Forms]![frm_Chart]![txt_TillDate])+#12/30/1899 22:59:59#) AND ((tbl_ChartData.MACHINE)=[Forms]![frm_Chart]![cbo_Machine]))
GROUP BY tbl_ChartData.MACHINE, tbl_ChartData.Shift_Order, tbl_ChartData.DT_Reason_Code, tbl_ChartData.DT_Reason, tbl_ChartData.Shift
HAVING (((tbl_ChartData.Shift) Like 'A'))
ORDER BY Sum(tbl_ChartData.[Count]) DESC;

I've seen this happen a few times recently. I ran this same query earlier this morning and got only 10 records.

Awaiting your answer.

[pbaldy]

If there's a tie, the TOP predicate will return more records. For instance, if I have SELECT TOP 1 but there are 2 records with the same value (in your case count), the query would return 2 records.

[Paul H]

Is there a fix for this? I need to return an exact 10 records for this report to work correctly.

[pbaldy]

Which record would you want returned in the event of a tie? Adding the appropriate field to the ORDER BY clause should resolve ties and give you exactly 10.

[Paul H]

I have to find some parameters that return extra rows to test this. I'll report back when I get the chance.

Thanks for the info.

[pbaldy]

No problem. I did a very brief test and it worked for me.

[Paul H]

You diagnosed the problem correctly, but that brought up another problem. For my query to return the top 10 records by Count descending I need the sort. If I remove it, I just get the first 10 records, not the top 10. I see where logic is going to fail because I have three identical values. The only solution I can see is building the data set in VBA/DAO and just stopping when the recordcount reaches 10. For my purposes the bottom ranked record is the least important bit of information.

[pbaldy]

I would still have the same primary sort, but add a secondary sort on a field that would be unique, like an ID field.

[Paul H]

Ah, I got it. This thing's been beating me up. I'm ready to move on.