Sorting on two fields in filter not working properly

[Bas de Geest]

Hi,

When I sort like this:
ORDER BY priority DESC, created ASC
so priority first and then created, the filtering works fine.

But when I turn this around, so created first and then priority, priority doesn't sort. Is there a reason for that?

[Nic Brough -Adaptavist- · Accepted solution]

I think there is one more thing to check here.

You didn't give us the issue keys, so I don't know if it is this, but look at the order the issues created on the 5th were done in - assuming they're all in one project, you'll find they increase in numeric order.

This is because the "created" field actually has a time as well - you can see it by hovering your cursor over the created dates in your screenshots.

I suspect you'll find your list is sorted by date and time, not just date!

[Ste Wright]

I think this might be the case!

I tested this, using this JQL:

project = TEST ORDER BY created ASC, priority DESC

^ The Priority looked like your screenshot @Bas de Geest (out of order) - but the created date was in order based on date and time. So technically, I think this is working correctly.

---

There is a workaround though:

• Created a custom Date Picker field to house the "Created Date" without the time
• Use an Automation Rule to populate this field
• Use the custom field in the JQL rather than the system field

You'd likely need to create two Automation Rules:

• Rule 1: Scheduled Trigger - to populate existing Issues
• Rule 2: Issue Created - to populate new Issues

Rule 1 would be a rule you run once, then deactivate/delete. Rule 2 would remain active to be used in all new Issues created in future.

---

The rule might look something like this:

• Trigger: Scheduled / Issue Created
• Action: Edit Issue
  • Field = <Custom Date Picker Field>
    • Value = {{created.convertToTimeZone("<Timezone Here>")}} 

Note: The "timezone" is needed because of this Bug - JRACLOUD-72466 - which can set the Date of the field back to UTC time, rather than your current timezone. This ensures the data is accurate

--

Once the data is populated, you can then use the Custom Field in your JQL:

project = TEST ORDER BY <Custom Field> ASC, priority DESC

^ This should then work more in line with what you expect in relation to your need :)

Ste

[Bas de Geest]

Hi all,

Thanx for your thoughts on this issue. Turns out that @Nic Brough -Adaptavist-  was right. Here's the reply from the Service Desk:

I accessed your instance and tested this, and actually found out that the sorting works as intended. Meaning, once you sort the issues by the Created field first, it sorts all issues with respect to the Created field. I believe what causes the confusion here is that the timestamp for the Created field is stored in msec precision in the Database, although it only displays in the days precision when searched, and minutes precision when hovered over with the mouse. 

The above explanation leads to a situation that, once you sort the issues by Created field, and because there are not many issues created at the same msec interval, the second field used for sorting - Priority will be neglected.

Regards,
Bas

[Alex Koxaras _Relational_]

Hi @Bas de Geest and welcome to the community,

The sorting option should work as expected. However, what I can see form the screenshots you provided is that:

• On the first screenshot you have sorted the results based on priority, while
• On the second you have sorted with created (check the dedicated arrow sign)

So what you see is quite normal.

[Fabio Racobaldo _Herzum_]

Hi @Bas de Geest ,

welcome to the Atlassian community!

It's weird. I just perfomed a test on my instance and it works correctly

Try to raise a support request through https://support.atlassian.com/contact/#/ so that someone on Atlassian side can check your instance.

Fabio

[Bas de Geest]

Hi @Fabio Racobaldo _Herzum_ 

Thanx! I raised a support request.