In-DB warning using ORDER BY window function SQL

[nakamott]

I am receiving the warning "Using order by in initial In-DB query may result in a query error. Consider using the sort on the sample or stream out tools."

I do not find much information on this error. Was wondering if it also applies to the ORDER BY used in window functions and what the error will be?

[AndrewBanh]

Hi @nakamott 

There are quite a few SQL queries that don't work in Alteryx, just as most Alteryx-formed SQL queries don't work in SSMS. Order by is one of said queries. I have only begun using In-DB tools as well 😊

If you are looking to sort your data, you'd have to configure your Sample In-DB like shown below. You want to change the top left box to Percent and type in 100 and tick the "Sample records based on order:" box.

Otherwise you'd have to stream your data out and use the normal Alteryx sort tool.

I have noticed that sometimes the Sample In-DB doesn't work as intended for me but the majority of the time it does. Haven't been able to work it out yet though.

Hope this helps mate.

- Andrew

[Raj_007]

Hi, 

I have few queries that has window functions like rank over parition by ,,,, and order by

when I try to run the query in indb connection i am getting an errror

This post is an old one but just wanted to confirm if it is still an iissue

[apathetichell]

tbh - order by usually works. It really depends upon your back end DB. Alteryx is basically saying use Order By as a sort when you are using datastream out. 

[Raj_007]

Hi, thank you so much - the way i have the queries are like derived tables

source is Oracle DB

WITH Test AS (

),

select from Test

something like this - the query structure is soemthing like this but too many queries - within that derived table or CTE i have the rank over partition by field and order by some field

so this is where i have the order by... so iam confused on how to get this as it is part of the query - I dont need any sort once i get the main dataset

[apathetichell]

You need to use subqueries vs CTE - WITH does not work In-DB because Alteryx wraps your entire statement in WITH command.

[Raj_007]

Thank you so much - really helpful

[simonaubert_bd]

Hello @nakamott  The warning is very conservative and even happen when you're doing rank with a windowing function directly in the connect in-db tool. But it's just that : a warning and not an error message. I have never encountered a case when it leads to an error.

Best regards,

Simon