How do I reverse sort order?

I have a query like this, displaying results as a table:
FROM … SELECT a, b, c WHERE a > 0 SINCE 1 day ago
I’d like the table to be sorted in descending order by value of a, which is numeric.
If I just run the query as above, it’s not sorted by the value of a (although I can click on the column name to sort it).

Just guessing, I tried adding “ORDER BY a” to the end of the query, and that works! But it’s ascending order, with the smallest value of a at the top of the table - the opposite of what I want. I tried guessing a few ways to change the sort order but just got syntax errors.

Searching newrelic’s documentation, I do not see anything saying ORDER BY even exists, I only find feature requests for it from a few years ago. The complete syntax summary for NRQL seems to be this: https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/nrql-syntax-clauses-functions - it doesn’t mention ORDER at all. The only mention of sorting is under FACET.

But “ORDER BY” is accepted, and does seem to have an effect. Have I stumbled onto an unannounced feature? Or am I just not finding the documentation for it?

1 Like

Hey @cos1 -

Great point about ORDER BY - I’ll take this over to the documentation team and get things updated.

To answer your question… I just did a quick test and and was able to add DESC after the order by value to reverse the sort order. Can you give that a try and let me know if it works?

4 Likes

Yes, that works, thanks! I had tried “DESCENDING” but had not thought to try “DESC”.

1 Like

BTW, a related question you may know the answer to, that I could not find in that documentation: If my table shows values like “20.1 k” for numbers, how can I tell it to just show the full number and not collapse it like that? I found documentation for setting account-wide defaults, but I don’t want to change anything account-wide, I want to put a chart on a dashboard and have it show the full numbers on that specific chart, always.

Hey @cos1 - the answer is no - it’s site wide or nothing… But this has been requested and I can add your plus one for our product teams.

1 Like

Although ORDER BY has been working for me with several queries, it does not seem to be working for me on this query:

FROM (FROM Metric SELECT max(aws_ta_cost) AS aws_cost WHERE Resource NOT LIKE ‘%Amazon %’ FACET Account, Resource LIMIT 100) SELECT max(aws_cost) AS monthly_cost WHERE aws_cost > 0 FACET Account, Resource ORDER by monthly_cost DESC SINCE 25 hours ago LIMIT 100

I thought it might be due to the fact that FACET is supposed to sort by the first field in the FACET, and maybe that overrides ORDER BY. However, the table isn’t actually sorted by Account either; it seems to be an arbitrary-looking order. Nonetheless, I tried this:

FROM (FROM Metric SELECT max(aws_ta_cost) AS aws_cost WHERE Resource NOT LIKE ‘%Amazon %’ FACET Account, Resource LIMIT 100) SELECT max(aws_cost) AS monthly_cost WHERE aws_cost > 0 FACET monthly_cost, Account, Resource ORDER by monthly_cost DESC SINCE 25 hours ago LIMIT 100

This puts the monthly_cost column first, but it’s still not sorted.

Is there a known bug where ORDER BY simply does not work when there is a FACET in the query? Or am I doing something wrong?

1 Like

Paging @pweber - this NRQL question has outpaced my abilities. Any thoughts for @cos1 on the above?

I’m curious. Is there a reason you are writing your NRQL with this ordering?

If you look at the documentation, you will find that ORDER BY is not documented. So if it works for you, yay! But it is not officially supported, so don’t count on it working with all queries.

1 Like

And that explains why it is NOT in the docs! Thanks Phil!

Ahh, that answers my earlier question,

Have I stumbled onto an unannounced feature? Or am I just not finding the documentation for it?

It looks like I have stumbled on an unannounced feature. Since I’ve seen feature requests for this going back several years, can you say whether this is going to be added and supported soon?

Hi, @cos1: Most of us work in training or customer support, so we have no insight (ha, ha) into planned features or ship dates.

1 Like

Even I have posted about it in the past. I agree it should be documented if it has been around this long. Perhaps its not stable and is inconsistent with the various functions.

That’s right @6MM - it is not consistent (because data types mostly). Was able to confirm that. So - you can always try it, but it might not always work.

I observe the same. It seems FACET is overriding ORDER BY . The field in the FACET doesn’t seem to follow a sorting order.

1 Like