COGNOiSe.com - The IBM Cognos Community

Hello.

I have a table as follows :-

REGIONÃ,  Ã, RANKÃ,  Ã, BRANCHÃ,  Ã,  Ã, SALES
NorthÃ,  Ã,  Ã,  1Ã,  Ã,  Ã,  Ã,  Ã,  GlasgowÃ,  Ã,  30000
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 4Ã,  Ã,  Ã,  Ã,  Ã,  AberdeenÃ,  10000
SouthÃ,  Ã,  Ã,  2Ã,  Ã,  Ã,  Ã,  Ã,  BristolÃ,  Ã,  Ã,  Ã,  25000
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 3Ã,  Ã,  Ã,  Ã,  Ã,  LondonÃ,  Ã,  Ã,  15000

I need to rank this by Sales within each region....any ideas of the required expression for this?

Gooch.Ã, 

Gooch,
Try changing your rank calculation to:

Rank([SALES] for Region).  This will give you ranking within each region. 

Hope this helps.  Keep us posted.

order by sales descending and rank from there. 

If you want a top 10 type thing try the following SQL


select * from
(select
  region,
  branch,
  sum(sales) as sales
from some_table
  group by
     region,
    branch
  order by sum(sales)  desc)
where rownum < 10