0

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*

 

ORG            SCORE

===              =====

DIV                11

Dept1            05

Dept2            10

DEpt3            20

 

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

 

CASE 

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?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.

    Like
  • R. A. Dawson Sr - seems like a fun challenge! Will departments be only ranked within their division or will they be ranked against each other regardless of division?

    Like
    • Joe Miller  DIV  is the top level in this case so all departments will roll up to a single division. 

      Like 1
  • R. A. Dawson Sr This can be done using MV.

    Can you review the solution shared in the attached screenshot.
     

    Like 1
Like Follow
  • 1 mth agoLast active
  • 4Replies
  • 32Views
  • 3 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.