I wanted to echo a thread from mid-2019 with no responses regarding window functions.
The ORDER BY clause appears to be ignored. The following example uses RANK, but the behavior has been equivalent for DENSE_RANK and ROW_NUMBER as well. The underlying table is just distinct dates in 2020 for which I had data in another table (used for demonstration purposes).
SELECT AsOfDate, RANK() OVER(ORDER BY AsOfDate DESC) AS days_past FROM dates
Below is a partial result and should suffice to demonstrate the issue’s existence. The earliest date in the set is given the lowest number and the field’s value increases from there:
I have tried explicitly ordering the table before applying the window function and not specifying an ORDER BY clause and seen the same results. The application of the window function appears to be applying some default ordering and not changing it.
I am using Dremio 4.1.4 Community Edition.