Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Is possible to ORDER BY four fields in JQL search?

Edited

It appears the 4th ORDER BY field is ignored.  Is this a known limitation or a bug?

The ORDER BY clause I am using is sprint ASC, issuetype ASC, parent ASC, rank ASC.

The full JQL is:
project = EFP AND sprint is not EMPTY AND status != Done ORDER BY sprint ASC, issuetype ASC, parent ASC, rank ASC

For 3 issues with the same sprint, issuetype and parent, I see that the rank values are in the wrong order:

  • 0|i00h3l (should be second of three)
  • 0|i00h3j (should be first of three)
  • 0|i00h3m (correct as third of three)

If I change the clause to sprint ASC, issuetype ASC, rank ASC, then rank order is correct.


Just for context, the reason I need this filter is because the Agile Backlog view that comes with a Scrum project does not allow me to filter out Done issues, nor can I add Quick Filters.

I learned that I can create my own board, where the "Board Settings" option is available and I can use a custom board filter, add Quick Filters, etc.  However, there appears to be a bug where sprints, issues and epic names are missing from that board.

3 answers

1 accepted

0 votes
Answer accepted

There are two things in your question that jump out at me:

1.  Rank does not *visibly* sort the way you think it does.  You should not be looking at the value in the field.  (I'm not saying the values you've given us are wrong or are not sorting the way you are assuming, it is a more general point that "order by rank" will work fine, but not the way you might think from the data in the field)

2. " the reason I need this filter is because the Agile Backlog view that comes with a Scrum project does not allow me to filter out Done issues, "

Er, no, that's wrong.  A backlog does not contain done issues.  There's something wrong in your process if your backlog has done issues in it.

1.  String values aside, the order of those 3 issues in my filter does not match the order in the "drag-n-droppable" Sprint.  

2.  I was referring to the Sprint section of the Backlog view, not the Backlog section (at the bottom of) the Backlog view.  Hope that clarifies the issue. 

The sort order in your sprint should be simple "order by rank", you can't do drag and drop if issues are not sorted by rank alone.

However, your initial question was right on the very first line.

The sort only considers the first three items in the order by clause!

Like # people like this

Thanks Nic for confirming that it's a known limit.

0 votes

Hi @Nick Pan ,

I've just tested a JQL query with 4 fields in the order clause and it seems to work. Do you have a date field in the 3rd order? Maybe even though they display as the same day, because they are different time in the day they are, they won't change based on the 4th field.

Hi @Carlos Garcia Navarro , thanks for your reply.  I've added details to my original post.  None of the fields I'm using are date fields.

0 votes
Pramodh M Community Leader Jan 06, 2022

Hi @Nick Pan 

Welcome to the Community!!

May I know the JQL that you are using, It should work as long as sorting is possible in the issues list one after the other.

Thanks,
Pramodh

Hi @Pramodh M , thanks for your reply.  I've added the JQL to my original post.

Pramodh M Community Leader Jan 07, 2022

@Nick Pan Now the question how do feel the query is returning the wrong result?

It should work as expected :-)

You have issues sorted in this order

sprint ASC, issuetype ASC, parent ASC, rank ASC

Thanks,
Pramodh

Hi @Pramodh M ,

I included in the original post the symptom I observed.  Namely, for 3 issues with the same sprint, issuetype and parent, the order of rank is incorrect. 

Pramodh M Community Leader Jan 10, 2022

Hi @Nick Pan 

May I know how you are considering the rank is incorrect?

Could you please include screenshots?

It basically takes the order of issues in your active sprint.

Thanks,
Pramodh

Hi @Pramodh M 

Please see screenshots below.  I have recreated the symptom using dummy issues.

1st screenshot - Stories in sprint are ordered (drag/drop) as 1, 2, 3.

2nd screenshot - ORDER BY rank shows 3, 2, 1.

(Note that I created the stories in order 3, 2, 1, as hinted by the keys.)

ss1.pngss2.png

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you