Rank a subset of a result set
This is an interesting use case - I have a set of departments reporting up to a division.
Each department is scored on a series of metrics and given a final score *as is the division*
The scores need to be ranked. The DIV should be ranked w/in the set ( rank = 2 in this example ) but the departments should be ranked only relative to other departments. ( Dept1 would be ranked 3 instead of 4 ).
There's a bunch more history around the rank function, but I think I can get there if I can write something like
WHEN ORG = 'DIV' <rank within the result set >
ELSE <rank within the subset of Depts>
Is it possible to apply the rank ( or other summary function ) to only a subset of the returned rows?
I'm wondering how robust an Internal Query Expression is and if I could use that with the "rank" function adding a where clause to the query.
It's just a touch beyond my reach at the moment, but if anyone is familiar w/ Internal Query Expressions and could point me in the direction of the correct syntax ( or if it's possible ) I'll appreciate it.