I am trying to return the top 5 records regardless of a tie. I am having trouble getting this to work. I have tried to use Top 5 Syntax but it doesn't suppress ties.
The goal is to have a report with flags displaying the top 5 (as this is all that fits on the page and I don't care about ties).... I have done this before and don't recall having this issue.
I have also tried using this SQL... However once the ID Count gets to the 5th record they record count hits 0.....so it will prevent me from filtering on 1 to 5...
Code:
SELECT (SELECT Count(t1.[CQ]) FROM [qry006-SO-HC-by-Location] t1 WHERE t1.[CQ]<t2.[CQ]) AS RowID, T2.Location, T2.Flag, T2.CQ, T2.LY, T2.[Pct Change], T2.Arrow
FROM [qry006-SO-HC-by-Location] AS T2
ORDER BY T2.CQ DESC;
Any help solving this issue would be much appreciated. Here is what the report looks like for reference (I set the columns to 5):