JQL order by with condition

Sophie Brodard

Hi,

I want to have JQL with the following order :  
- all issue without timespent first (condition: timespent IS EMPTY) 
- then list issue ordered by updatedDate DESC

Example of wanted ordered data:
* 1) timespent IS EMPTY, updatedDate = 11/10/2024
* 2) timespent IS EMPTY, updatedDate = 10/10/2024
* 3) timespent 1week , updatedDate = 11/10/2024
* 4) timespent 3week , updatedDate = 10/10/2024
* 5) timespent 2week , updatedDate = 09/10/2024

With a SQL it would have done something like :

"assignee = currentUser() ORDER BY (timespent IS EMPTY) DESC, updatedDate DESC"

But such syntax is not allowed in JQL, so, how to do that in JQL ?

Thanks

Humashankar VJ

Hi @Sophie Brodard 

While JQL doesn't support conditional ordering or complex ORDER BY clauses like SQL, there are options you can explore to see your desired ordering.

Idea-1:

Create a custom field ("SortOrder") and use automation to set its value based on specific conditions. Then, use this field in your ORDER BY clause, such as: text assignee = currentUser() ORDER BY SortOrder DESC, updatedDate DESC. Automation sets SortOrder to 1 if timespent IS EMPTY and 0 otherwise.

Idea-2:

Try using two separate queries and combine their results,

Query 1: text assignee = currentUser() AND timespent IS EMPTY ORDER BY updatedDate DESC

Query 2: text assignee = currentUser() AND timespent IS NOT EMPTY ORDER BY updatedDate DESC

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards