Return Top 5 in DESC Order regardless of Tie

[mrmmickle1]

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...

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):

[mrmmickle1]

I just created a new query based on the previous query and then used top 5 syntax. Seemed to work without issue. Seems pretty odd to me..... can anyone help to explain how the database is logically processing this stuff?

[Missinglinq]

I don't do a lot with this kind of thing...but instead of running a simple Select query couldn't you run a Select Unique query...then run you do the TOP 5 thing?

Linq ;0)>

[mrmmickle1]

Hey Missinglinq-

I initially tried to do SELECT DISTINCT as you referenced and I still got the same results. Glad I was able to figure out a workaround. I appreciate your input.