Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Feature Idea: ORDER BY for NRQL

tag

#1

I’d like to order a table by my own preference, like with an ascending APDEX value, rather than having to place it after a column that follows the inverted Apdex results to get the worst results at the top.

  • I want this, too
  • I have more info to share (reply below)
  • I have a solution for this

0 voters


Do we have any feature to do Order By
TIMESERIES with LIMIT applies random sorting
Wrong sorting order of numerical strings
#2

Hi @AndrewWalker - This is a current but undocumented feature. Although I have noticed some inconsistencies.


#3

Thanks for the response Stefan.

Ok, so if I type order by and then column name then it will work? It doesn’t seem to with the following query:

SELECT percentage(count(*), WHERE duration > 28000 or result = 'FAILED' ) as '% (INCL. FAILURES)' , percentage(count(*), WHERE duration > 28000 or result = 'FAILED' )/100*720 as 'HOURS (INCL. FAILURES)' , percentage(count(*), WHERE duration > 28000 or result = 'FAILED' )/100*720*60 as 'MINUTES (INCL. FAILURES)' , percentage(count(*), WHERE duration > 28000) as '% (EXCL. FAILURES)' , ((percentage(count(*), WHERE duration <= 7000 and result = 'SUCCESS' )+percentage(count(*), WHERE (duration > 7000 and duration < 28000) and result = 'SUCCESS' )/2))/100 as 'APDEX Score' FROM SyntheticCheck SINCE 30 days ago FACET monitorName ORDER BY ((percentage(count(*), WHERE duration <= 7000 and result = 'SUCCESS' )+percentage(count(*), WHERE (duration > 7000 and duration < 28000) and result = 'SUCCESS' )/2))/100 asc

Having looked into this it appears that functions won’t work with the ORDER BY and the ORDER BY is underlined in red when it is used.

Needless to say that this is not a complete feature and virtually useless in any faceted query. Come to think of it I have no idea what it’s doing, perhaps it’s ordering by the sum of duration if I order by duration.

Also I might add that having an undocumented feature is a very bad idea when your system uses syntax checking and suggestions. Only people who are told, “yes, it’ll work even though it doesn’t think it will”, will even try it surely.


#4

Having looked at your query, I think your calculated field ordering is definitely not supported. As I stated, it is not official functionality and this is probably a use case which has not been detailed yet. I have managed to get the ordering to work with simple attributes or the built-in functions such as average, etc.


#5

Looks to me like average doesn’t work for me.


#6

I have added a poll so we can collect votes around this, @AndrewWalker! Be sure to add yours above! :blush:


#7

I’m not sure if this is quite the same feature… however I’d like to be able to do ORDER BY on events like duration. Knowing the fastest/slowest URLs (of a given query) is a pretty key metric to me.


#8

Thanks for sharing @scunliffe! I will add it to the official request and let the Insights Product Manager decide where it belongs. :thumbsup: Thanks!


#9

I would like to use FACET clause with ORDER BY and LIMIT clauses so I could order with values asc or desc.

SELECT count(*) as 'TransactionCount' FROM Transaction FACET containerId 
WHERE appName = 'xxxx' and name = 'yyyy' SINCE 2 minute ago 
UNTIL 1 minute ago ORDER BY 'TransactionCount' desc LIMIT 1

#10

Thanks @seckin.pulatkan - I’ll get that request forwarded for you :slight_smile:


#11

Looks to be a really popular request open since 2017. Any word on progress, even if in as-of-yet undocumented feature form?

My NRQL request is near identical to @seckin.pulatkan’s above:

FROM Transaction SELECT count(*) FACET `host`

Given LIMIT appears to be deterministic, I’d like to order ASCending to highlight hosts with the least number of transactions at the top. If we’re being adventurous, maybe even display just the Top/Bottom 5.


One workaround I came across is multiplying by -1 to reverse FACET's usual descending ordered output, then applying LIMIT to get the “top” lowest results.

FROM Transaction SELECT count(*)*-1 FACET `host` LIMIT 5

While the output is ordered as needed, it renders terribly in the dashboard (both Insights & NR1) as the bars are all full… Not to mention the numbers are negative.

Adding support for Order by … ASC/DESC and Top/Bottom n keywords would be very appreciated.

Thanks.


APM: Alert on hosts output
#12

I’m afraid I have no update yet. I understand the workaround you are currently working with is far from ideal, right now I don’t have a better one for you. But I’ll make sure your feedback is heard by the right team here.