Rank & Global Rank within MySQL

Emma Edwards May 1, 2014

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
Atlassian Team members are employees working across the company in a wide variety of roles.
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.

Emma Edwards May 27, 2014

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

Emma Edwards May 27, 2014

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 votes
Andy Nguyen
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
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.

Emma Edwards May 25, 2014

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

0 votes
Andy Nguyen
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 3, 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.

Emma Edwards May 25, 2014

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

Suggest an answer

Log in or Sign up to answer