Enhancement to RANK function when dealing with tied rankings

karl.mcphee

It would be really useful for certain cases if the RANK function could generate a sequential output when dealing with tied data.  

 

Currently, the RANK function can deal with tied entries in 4 ways, MAXIMUM, MINIMUM, AVERAGE and SEQUENTIAL and all handle tied data in a slightly different way but do not generate an output in which items are ranked as 1,2,3,4,5 in an incremental way.  The output skips rankings when using MAXIMUM, MINIMUM and AVERAGE and ignores tied rankings when using SEQUENTIAL.

 

This is a bit difficult to explain in words so please see the attached image showing the outputs currently generated by RANK and the desired output which it would be good to add as an enhancement to the RANK function.

 

If any members of this community fancy a challenge and can show an alternative neat and performant way of generating this output then I'd love to hear it!

Miran

Status changed to: New

TimWard70

I think this is summarised as adding a new option to the rank function "Minium_Sequential"

Where there are two or more values tied at a particular rank the rank for the next value would be one lower than the preceding rank, rather than 2 lower as is currently the case with the "Minimum" option.

 

For example:

 

Value  Rank

20       1

20       1

20       1

15       2

10       3

10       3

5         4