Using Top N as a Filter in SSRS

Question

Post reply

Using Top N as a Filter in SSRS

esto4273

Old Hand

Points: 311
More actions
September 21, 2009 at 12:40 pm

#385400

Does Top N sometimes deliver more than the desired count, for instance if there is a tie?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

psvirin Mr or Mrs. 500 Points: 542 More actions · Answer 1

Hmm...yes the SELECT TOP WITH TIES returns sometimes more than N values.

It returns all of the lines which have the same value of the ordering column as the one from the N-th line.

But maybe I have not understood the question?

-------------------

StarWind Software developer ( http://www.starwindsoftware.com )

esto4273 Old Hand Points: 311 More actions · Answer 2

Thanks. It could be that I don't understand what it should be doing too.

A former collague used it to show the top 10 accounts. The result set is essentially a customer list coming from a stored proc which drives several reports. We have a few RDLs off that set which list and count the reuslts in different ways.

He successfully used a filtered group in a table to show top 10 countries, but I think he got lucky.

My result set filters (top n = 10) the counts down to a month, where I have several groups with the same count:

1

2

3

4

5

6

7

8

9

10

It seems to work when the results are unique, but shows more than "the top ten" when I have ties within the top set.

Is my understanding right then?

JanTimmermans SSC Enthusiast Points: 168 More actions · Answer 3

I've managed it by adding a field in the dataset with a row_number() over(order by ...) statement in it.

And then filter on this newly added field, <= 10.

Works fine...

/Jan

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

What do you do when you have a dataset that several items are using, and you want one of the tables to only show the top 20 detail records?

Using Top N as a Filter in SSRS

Cookies on SQLServerCentral