Using JQL to find max value of custom field in JIRA

Other Questions Jira
1

Hey everyone! I’m stuck on a JIRA JQL problem. I need to find defects and stories in my project with the highest value in a custom field (let’s call it MyNumber).

Here’s what I’m trying to do:

  • Query defects and stories
  • Look at MyNumber (it’s different for each ticket)
  • Get tickets with the highest MyNumber value

For example, if I have:

  • Ticket A: MyNumber = 10
  • Ticket B: MyNumber = 9
  • Ticket C: MyNumber = 10

I want to get Tickets A and C.

I’ve tried this query, but it’s not working:

project = 'MyProject' 
AND type in (Defect, Story) 
AND MyNumber is not EMPTY 
AND status = 'Done' 
AND assignee in (me, 'John', 'Jane')
ORDER BY MyNumber DESC

How can I modify this to get only the max MyNumber tickets? Thanks for any help!

2

hey sophia, i feel ur pain. jira is tricky. try running the query with order by MyNumber desc, copy the top value, then re-run filtering on MyNumber equals that value. not perfect but gets the job done. good luck!

3

Your approach is on the right track, but JQL doesn’t support finding max values directly. A workaround is to use two separate queries. First, run your existing query with ‘ORDER BY MyNumber DESC’ and note the highest value. Then, modify your query to include ‘AND MyNumber = [highest_value]’. This method will return all tickets with the maximum MyNumber.

For better performance, especially in large projects, consider creating a saved filter for this query. You can then use JQL functions like ‘issueFunction’ to reference this filter in more complex queries. This approach can significantly reduce query execution time for frequently used complex searches.

4

I faced a similar challenge not long ago. Instead of trying to get the maximum value directly in a single JQL query, I split the task into two parts. The first step is to run a query that orders the issues by MyNumber in descending order and then limits the result to just one ticket. This step gives you the highest MyNumber value. Then, you use that number in a second query to filter for all tickets with that exact value. Although it requires two separate queries, this workaround is necessary since JQL doesn’t support aggregate functions for finding maximum values.