Hello, I have an issue with trying to pull the top 10 and in some cases I have rows coming back because of the same rank.
Example:
Instruments age-group counts rank
guitars 18-25 15 1
guitars 26-35 10 1
drums 18-25 20 1
drums 18-25 5 1
bass 26-35 7 2
bass 18-25 3 2
How would I get the distinct rank. I tired a running-count but I am getting group by errors when I throw this data item in my filter.
Any help is appreciated. Thanks
This might help :
http://www-01.ibm.com/support/docview.wss?uid=swg21339207 (http://www-01.ibm.com/support/docview.wss?uid=swg21339207)
Thanks for your post. What is throwing me off is the 'age-group' column for each of the instrument rows.
I was able to capture the SQL generated and I believe if I can add 'instruments' to order by within the over clause I will get the desired results. Is this possible?
DENSE_RANK()
OVER(
ORDER BY
SUM("SQ0_Query1"."Count") DESC, instruments ASC
have you tried sorting directly form the report one column at a time?
Yeah this adds the sort to the end of the query. I'll keep looking into it
What is the expression you are using for your rank calculation?
I'm trying this and I believe it should get me what I want but when I add in my column 'age group' I receive the below error.
'The column rank must be contained in an aggregate function or the GROUP BY clause.'
expression: 'rank'
rank(total([Count] for 'col') DESC, 'col' ASC)
'Col' is an example. Thanks for the suggestions as I am new to cognos.