Hopefully someone can set me straight here - I am looking for a way to select the top n items, so long as the n+1th item does NOT have the same value as the nth item.
Basically, I'm looking for the opposite of:
SELECT TOP 5 WITH TIES col1
ORDER BY col1
If sorting all values of col1 would give these values: 5,4,3,2,1,1,1
... then the WITH TIES query above would return 7 rows...
I'm looking for a query that would return only 4 rows: 5,4,3,2
Since including all of the 1's would push it over the limit of n rows, I want to exclude ALL rows with this value, not just the one that happens to get sorted first.
Basically, I want the TOP n, except for when the TOP WITH TIES n will have more than n rows.
I hope I'm making sense here - as I feel there's got to be a clever way to do this that I'm just not seeing immediately.