Chart "Top N"

With 2k5 I am trying to make the "Top N" operator, used in a chart "category," return only N categories. But it almost always returns more than N categories. Can someone explain the nuances of the Top N operator, so I can get past this?

Is N fixed number, If yes then you can try filtering on rownumber.

One more option is to create a different dataset and then use "select top 10" syntax in query itself.

HTH1

Priyank

|||

Whether N is fixed or not, "rownumber" does not correlate to chart "category" grouping. I want the top 10 categories, not the top ten rows.

I don't want to use "select top N" in my data set, because it would require refactoring my query to use a "group by" expression. What I want is for the grouping to be handled by the chart.

So this brings me back to the beginning. Can someone explain the highly erratic behavior of the "top N" operator? It sometimes returns more than N, sometimes less, and it often screws up the sort order. Is this something fixed in a SSRS patch?

|||

Ok, I figured it out. First of all, assuming the perspective of "order ascending," "Top N" actually means "Bottom N," and "Bottom N" actually means "Top N." Go figure. Also, the "Top N" operator, at least when used with a chart "category" filter, does the following:

1) Uses the category field expression to select the first row of each category

2) Sorts the selection of "first rows" that came from the category, based on the field expression that was given to the "Top N" operator itself

3) Performs a dense_rank() function on that output

4) Selects the "Top N" of the dense rank

5) Afterward, the "Sorting" spec of the category is applied

This is why I sometimes get more than "N" categories returned.

charindex faster than like operator?

Hi ppl,
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')