How do I create a Rank in Confluence Table Transformer if the Rank function is not available to me.

Joanna Weir

Just like in excel, I need RANK data to returns the order (or rank) of a numeric value compared to other values in the same list. In other words, it tells you which value is the highest, the second highest, etc. 

But my Confluence Table Transformer does not allow the Rank() function.   So I need help with an alternative query.

My data has Team and Score and I expect rank to look like this: 

Team        Score        Rank

Team A       60            1

Team B       63             2

Team C       63             2         << same because it has same score as above

Team D      65             3

Katerina Rudkovskaya _Stiltsoft_

Hi @Joanna Weir

You can try the following SQL query in Table Transformer:

SELECT
T1.*,
(SELECT COUNT(DISTINCT T2.'Score') + 1
FROM T1 T2
WHERE T2.'Score' > T1.'Score') AS 'Rank'
FROM T1