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

[Sophie Brodard]

thanks I 'll try

[Jim Knepley - ReleaseTEAM]

Hi @Sophie Brodard 

You're correct that you can't use a condition as part of an ORDER BY clause.

I think null fields appear first when in descending order, which some people think is wrong. In your case, it's the behavior you want, so...

ORDER BY timespent DESC, updateDate DESC

...could be what you're looking for.

[Sophie Brodard]

thanks, but sorry It's not what I want.  If timespent is set, I do not want order by timespent but by updatedDate.... 

In my example I do not want this order : 

* 1) timespent IS EMPTY, updatedDate = 11/10/2024
* 2) timespent IS EMPTY, updatedDate = 10/10/2024
* 4) timespent 3week , updatedDate = 10/10/2024
* 5) timespent 2week , updatedDate = 09/10/2024
* 3) timespent 1week , updatedDate = 11/10/2024