Get Distinct Rank

[NewGuy1983]

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

[sdf]

This might help :

http://www-01.ibm.com/support/docview.wss?uid=swg21339207

[NewGuy1983]

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

[sdf]

have you tried sorting directly form the report one column at a time?

[NewGuy1983]

Yeah this adds the sort to the end of the query. I'll keep looking into it

[Lynn]

What is the expression you are using for your rank calculation?

[NewGuy1983]

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.