Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Joanna Weir August 8, 2023

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

1 answer

1 accepted

3 votes
Answer accepted
Katerina Rudkovskaya _Stiltsoft_
Atlassian Partner
August 11, 2023

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

2023-08-11_20h27_52.png2023-08-11_20h28_01.png

Joanna Weir August 15, 2023

PERFECT!!!   Thank you SO Much.

Like Stiltsoft support likes this
Aravind Pai
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 11, 2025

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
Contributor
June 17, 2025

Hello Aravind,

It should be possible, indeed:
Use case.png
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:
Use case 2.png
Hopefully, this helps.

Best wishes,
Nikita

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events