Ordering in Window Functions

Table of Contents

ORDER BY in OVER Clause

Just like for query’s ORDER BY, one or more OverOrderBy calls translate into ORDER BY items added to an inline OVER clause instead. The following query is using the same fields and sort order in the third column, as in query’s Sort. For each Manager’s ID in ascending order, we select all his/her Employee names in ascending order:

Our generic LIST aggregate window function translates into GROUP_CONCAT calls for databases such as MySQL, MariaDB or SQLite. The function takes all row values up to the current row and concatenates them in a comma-separated list. As both query’s order and List’s order are the same, last column’s values follow second column’s values up to that point. Here is the generated SQL:

SELECT MGR, ENAME,
   GROUP_CONCAT(ENAME, ', ') OVER (ORDER BY MGR, ENAME) AS ENAME_1
FROM emp
ORDER BY MGR, ENAME

OverOrderBy is not a real function, but we encapsulate each item of an OVER ORDER BY clause in such a call. First argument is can be a field name or another column name. To use it with complex expressions, define the expression in a separate column, and reference that column as argument in function’s call. Call it once if you sort by one field or column expression, multiple time if you sort by more than one item.

ORDER BY with NULLS

Second argument of an OverOrderBy call is by default ASC and that’s an internal keyword for the sort type. All ORDER BY clauses in Data Xtractor, from a query or OVER clause, support sort with NULLs. Some database systems support the standard SQL keywords NULLS FIRST or NULLS LAST, added at the end of each sort item. If not, we always emulate them.

Following changed query moved all NULL occurrences at the end of the ascending sorted MGR list. To keep the data view consistent, we did the same to the first OverOrderBy call. ASC NULLS LAST keyword and the specific sort icon tells us how NULLs are involved:

Here is the generated SQL:

SELECT MGR, ENAME,
   GROUP_CONCAT(ENAME, ', ') OVER (
      ORDER BY CASE WHEN MGR IS NULL THEN 1 ELSE 0 END, MGR, ENAME) AS ENAME_1
FROM emp
ORDER BY CASE WHEN MGR IS NULL THEN 1 ELSE 0 END, MGR, ENAME

ORDER BY Configuration Popup

OverOrderBy calls are configured through a specialized popup. Click on the cell icon of your OverOrderBy call, and select the Open Configuration Popup menu command. Here is what you’ll see for your first call:

Remark how the sort with NULL options are always present. For our current selection, in our SQLite database, we’re told our database has no native support for this feature, but it will be automatically emulated.

OVER ORDER BY vs Query’s ORDER BY

Query’s ORDER BY sort order and the OVER ORDER BY clauses do not have to be similar, as we kept them until now. There is no relationship between their sort order, other that they may use the same fields in similar ways. In the following query we removed completely the MGR column and sorted ENAME descending. While in our window function call we kept everything in place just like before:

The rows in the first column appear in a different order. But follow their related second column values: you’ll see that mapping remained basically the same: FORD still maps to “FORD”, ALLEN to “FORD, SCOTT, ALLEN” and so on.

ORDER BY from Shared WINDOW

OVER clauses can be declared like templates in separate window definition columns, and referenced from columns with actual window function calls, with Over(column_name). This allows the same window definitions to be reused by more than one active column.

Following query continues current OVER clause’s definition with whatever we defined in the window definition column w. Basically, with an emulated WINDOW clause, this will return a query with the exact same syntax as before, because the second OverOrderBy call will simply replace the Over(w) call:

With an emulated WINDOW clause, here is a possible generated SQL for SQLite:

SELECT ENAME,
   GROUP_CONCAT(ENAME, ', ') OVER (
      ORDER BY CASE WHEN MGR IS NULL THEN 1 ELSE 0 END, MGR, ENAME) AS ENAME_1
FROM emp
ORDER BY ENAME DESC

The shared window definition feature is present in Data Xtractor whether or not the standard SQL WINDOW clause is supported by your database. When it is, and you don’t want it emulated, we generate a WINDOW definition for such template columns, and reference them in SQL as well from the OVER clauses.