Rank & Global Rank within MySQL

I'm trying to get the Rank and Global Rank Values from a report matched in the Database via MySQL.

From searching I believe the table is ao_60db71_issueranking however the values stored in this table are not matching the report.

A person in this forum was going to raise a support ticket for a similar issue but didn't post their outcome. https://answers.atlassian.com/questions/206455/how-do-i-get-rank-field-directly-in-database-any-sql-can-do-this

the values in the table for the issue do not match the export report against Rank & global rank columns can anyone advice where to look or is this a bug of some sort ?

Thanks

3 answers

1 vote
Andy Nguyen Atlassian Team May 27, 2014

Hi Emma,

I'd have to say sorry as I have little knowledge of how this works. If you can dedicate more time, please have a look into the following aspects:

  • compare the number of rows in table jiraissue and table ao_60db71_issueranking (might not be equal if there're deleted issues)
  • the following query will give you the list of existing issues in issue ranking table:
SELECT * FROM ao_60db71_issueranking where issue_id in (SELECT id FROM jiraissue)
  • the following query will let you know the highest ranked issue:
SELECT * FROM ao_60db71_issueranking where issue_id not in (SELECT next_id FROM ao_60db71_issueranking where next_id is not null)
  • kindly continue from here as I have no more clue at this step

All the best.

Thanks for your help, much appreciated I will take a look and hopefully I can figure it out. Will let you know. thanks Em

Hi, I'm wondering if I have given the wrong info.

So perhaps I can explain further.

On doing the Export to Excel File there are two columns called Rank & global rank that are exported.

For example.

IssueId = 67954 in my exported file Rank = 48322, Global Rank = 46781

On looking at th eao_60db71_issueranking table there are 2 results in there for IssueID = 67954

CUSTOM_FIELD_ID ID ISSUE_ID NEXT_ID
10070 98905 67954 67955
10370 98906 67954 67956

I am uncertain as to where the values of 48322, 46781 are coming from in the export file these are the values I'm trying to track down.

0 vote
Andy Nguyen Atlassian Team May 03, 2014

Hi Emma,

The values of ID column in ao_60db71_issueranking are not the same as the values of Rank column in JIRA:

  • Rank column in JIRA shows the real ranking values of issues from 1 onwards
  • ID column in ao_60db71_issueranking merely lists the database entries from 1 onwards, whenever there's a new issue ranking/re-ranking (ID is the primary key of the table)

You can verify this by adding a new issue in JIRA and then refreshing the ao_60db71_issueranking table. You will see a new ID entry added linking an ISSUE_ID with a NEXT_ID.

Basically ao_60db71_issueranking shows which issue is ranked over which issue, sequentially. It does not show the real ranking values of the issues. You can only base on ISSUE_ID to match between the 2 columns, e.g. what is the ISSUE_ID of the highest ranked issue (ranked 1) in JIRA.

Cheers.

Sorry for the delayed response I have just returned from holiday.

Thanks for your reply, however would you be able to tell me where in mySQL I determine the Rank ? My understanding is you're saying the ao_60db71_issueranking is not related to this process.

Where would I find the Rank or Global Rank against an Issue ?

thanks

0 vote
Andy Nguyen Atlassian Team May 25, 2014

Hi Emma,

You won't find the real ranking values in database but rather in JIRA's Issue Navigator. From the List view, configure Columns and add the Rank field to the List view. You will see the correct ranking values as in the exported file.

Cheers.

Andy,

Thank you however my issue is not that I want to see the ranking from within the JIRA application or front end.

I think your last reply relates to viewing this within the Jira application unless I have misunderstood.

I am creating my own export as per the exported file, by writing a Mysql Query.

I want to export a replicated version of the export file using my own code and querying the tables directly.

I think you're telling me this information is not stored in a table, so is this a calculation based on certain criteria for example based on other data within the database.

I am sorry my knowledge is very limited I'm new to Jira.

Thanks

Suggest an answer

Log in or Join to answer
Community showcase
Emilee Spencer
Published yesterday in Marketplace Apps

Marketplace Spotlight: DeepAffects

Hello Atlassian Community! My name is Emilee, and I’m a Product Marketing Manager for the Marketplace team. Starting with this post, I'm kicking off a monthly series of Spotlights to highlight Ma...

37 views 0 3
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot