im getting an error while running a sql query in db connect input

[raghu0463]

im trying to run this query in splunk db connect input, in editor mode, the below
query with order by clause is giving the errors
if im running this query without the the order by clause its running perfectly

select distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date
from customer ca left join org or on ca.org = or.[Org]
order by DateDiff(day, ca.Queue_Date, GETDATE( ))

below is the error im getting

External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (select distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date from customer ca left join org or on ca.org = ro.[Org] order by DateDiff(day, ca.Queue_Date, GETDATE( ))) t", params: "None", caused by: Exception(' java.sql.SQLException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified..',). "

[tmuth_splunk]

First step in debugging a query with DBX: run it in a non-Splunk tool. In this case, whatever tool you use for SQL Server (just guessing). I suspect you'll get the same error in this case.

Thy this:

select TOP 100 PERCENT distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date
from customer ca left join org or on ca.org = or.[Org] 
order by DateDiff(day, ca.Queue_Date, GETDATE( ))

[raghu0463]

my doubt is does order by clause work in db connect ??
In some other cases where I'm executing sql queries in db connect which are having variables, CTE's are also giving errors.