ORDER BY with experessions or functions

Question

I want to put tickets with some selected status at the end. A am looking for some sort of expressions inside ORDER BY:

assignee = currentUser() AND status != Erledigt ORDER BY (status == Zurückgestellt)

SQL would evaluate last expression to "1" of "0" and use it for ordering.

JIRA doesn't accept it:

Error in the JQL Query: Expecting a field name but got '('. You must surround '(' in quotation marks to use it as a field name. (line 1, character 90)

Aron Gombas _Midori_ · Answer

As @Alexandre Pezzini explained, you can't do this out of the box.

But you can:

* You can create a Number type custom field "Weight". Number is a great choice, because ordering is untuitive.
* Using an automation rule (or a script), set this field's value to 0 if status == "Zurückgestellt" and 1 otherwise every time when the status changes. (You can complicate the logic and use more than just two different weight values.)
* In CQL use this: "order by Weight".

Should be straight-forward.

Alexandre Pezzini · Answer

Hi @Rodion Alukhanov ,

Welcome to the Atlassian Community!

The issue is that you can't use a condition or a function at the ORDER BY . It would be possible if JQL accepts like CASE WHEN in ORDER BY, but that isn't allowed.

To reach what you want, would be best if you use a REST API, then you can use a JQL to limit the statuses, and once you gather the issues you can order in your code as you wish.

But feel free to raise a Feature Request if you wish this feature to be implemented.

Just be aware that there are a number of factors that determine how Atlassian prioritizes suggestions. You can learn more about this by reading our Implementation of New Features Policy.