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_ · Accepted solution]

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

[Joanna Weir]

PERFECT!!!   Thank you SO Much.

[Aravind Pai]

Is it possible to do this if "T1" is a subquery?

So I have something like:

SELECT
  COMBINED.'Team',
  COMBINED.'Score'
FROM
(QUERY1
UNION ALL
QUERY2) AS COMBINED

Now I want to have the rank of the teams in that combined table. 

[Nikita Kamai]

Hello Aravind,

It should be possible, indeed:

The SQL can be as follows:

SELECT
COMBINED.'Team',
COMBINED.'Score',
(SELECT COUNT(DISTINCT SUBQUERY.'Score') + 1
FROM (SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS SUBQUERY
WHERE SUBQUERY.'Score' > COMBINED.'Score') AS 'Rank'
FROM
(SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS COMBINED

ORDER BY 'Rank'

The output can be:

Hopefully, this helps.

Best wishes,
Nikita