Hi,
I have a nested query to create a table chart. I use ORDER BY to sort the table using one of the columns, but when I do this the column gets duplicated and added at the end. How can I prevent this form happening?
Hi @lorenzo.fay - Welcome to the community.
Would you provide your query, removing any sensitive data, as this will help us help you 
1 Like
Sure.
FROM (FROM Api SELECT sum(provider.count.Sum) AS 'Total requests', percentile(provider.latency.Average, 50) as 'Median', percentile(provider.latency.Average, 85) as 'PCTL 85', percentile(provider.latency.Average, 95) as 'PCTL 95', percentile(provider.latency.Average, 99.9) as 'PCTL 99.9', ceil(sum(provider.count.Sum) * 0.001) AS 'requests over 99.9%', max(provider.latency.Maximum) AS 'MaxLatency' FACET displayName LIMIT MAX WHERE provider.latency.Average > 0 AND providerAccountId = '000' AND entityName != ('A') AND entityName != ('B') AND displayName != 'C' WHERE provider = 'D') SELECT displayName, Total requests, getField(Median, '50') AS 'Median', getField(PCTL 85, '85') AS 'PCTL 85', getField(PCTL 95, '95') AS 'PCTL 95', getField(PCTL 99.9, '99.9') AS PCTL 99,9, requests over 99.9%, timestamp ORDER BY PCTL 99.9 DESC SINCE 1 day ago
Hello @lorenzo.fay , it seems that the ORDER BY clause is not aware of the aliases, so it will add the original column to the results chart, then use it to order the results.
I couldn’t get the query given above to work, but I produced a minimal example below to demonstrate the issue (ORDER BY adds an extra column when the column has been aliased):
FROM NrConsumption SELECT metric AS 'example' since 1 day ago ORDER BY metric
When the alias is removed, ORDER BY does not add a column to the results chart:
FROM NrConsumption SELECT metric since 1 day ago ORDER BY metric
I’m not aware of a specific workaround for this (clicking column headers to order results isn’t a great solution for dashboards), but you may be able to get a better outcome for the chart by using FACET...ORDER BY as described in the NRQL docs.
1 Like