Cart

Sorting Data

Table of Contents

Basic Sorting

One column’s data may be returned sorted in Ascending or Descending order. When more than one column sorts data, we also need the order in which data is sorted. The next query starts by sorting data by (1) Manager’s Name, then by the (2) HireDate of each employee, then by his/her (3) Salary and finally by the (4) Job name.

When you remove a column from sorting, or change a sort order number, the 1, 2, 3… order is eventually recalculated. When you add a new column to the sort list, it is added last in the sort order.

sorting-basic

Sorting data is specified in the ORDER BY clause of the generated SQL. The list may contain:

  • ASC or DESC for each item, depending how data is sorted (by default ASC).
  • selected field names or expressions, as they appear in the SELECT list (ORDER BY emp2.MGRNAME, emp.HIREDATE DESC, emp.SAL DESC, emp.JOB).
  • select aliases (when supported), to avoid repeating expressions or long qualified field names (ORDER BY mname, hdate DESC, sal DESC, job).
  • ordinal numbers (when supported) of the field or expression in the SELECT list (ORDER BY 3, 4 DESC, 5 DESC, 2).

Sort by Expression

There are two main approaches when you don’t want to sort by a field, but rather by a calculated expression. First approach is to define a Formula for your Sort expression, as here below. This formula is not just for the ORDER BY clause, it will also appear in the SELECT list, and ORDER BY will still refer to this SELECT expression by ordinal position or alias, like in ORDER BY 1.

sort-by-expression

Second approach is to hide the expression from the SELECT list (just uncheck the Selection checkbox). This tells your SQL generator that you created the expression for the ORDER BY, GROUP BY or another clause, not the SELECT list. And in your generated SQL, ORDER BY will now be followed by the full definition of the expression itself, like in ORDER BY EXTRACT(MONTH FROM emp_.”HIREDATE”).

Sorting with Nulls

We’re all familiar with the notion of sorting in ascending or descending order some column values. But what if the column has NULLs? Do they come first or last in our ordered result? Well, it depends… On the database type, on the possibility of using a NULLS FIRST or LAST operator, on the default configuration etc.

We simplified it and made it generic for all database types! With one single switch (by default ON), you may extend the obvious Ascending and Descending options from the Sort sub-builder (or, by default, “(no sort)”). We added four different new options, so you can fully control your result set with no regard of the database type and all other factors:

  • Ascending (Nulls First)
  • Ascending (Nulls Last)
  • Descending (Nulls First)
  • Descending (Nulls Last)

Here is the Sort menu of the query builder in this expanded form, and on the right a result set after we chose Ascending (Nulls Last). This was a SQL Server query and, with just Ascending, Nulls were returned first by default:

sort-with-nulls

Random Rows

To return rows in a different order each time the query is executed, you have to associate a randomly generated number with each row, and ORDER BY that number. Your SQL query will look like SELECT RandomNumber() AS rn, … ORDER BY rn, …

We already offer a RandomNumber global function – that hides implementation details of each platform – that you can call instead. Our implementation relies on the specific database server random function, but makes sure a new random number between 0 and 1 is generated for each row, and that executing the query again doesn’t generate the same sequence of random numbers.

random-select

It usually makes sense to hide the random query expression. You can do this through the Selection sub-builder. But we also offer a shortcut, a simple Random Rows Order toolbar button you can press, and your random number is automatically and transparently added in front of the ORDER BY clause (you don’t actually need it in SELECT), in the form SELECT …. ORDER BY RandomNumber(), … Check the Random Rows Order demo query, it does just that on the FruitU table of constants.

While RandomNumber() also generates unique numbers, whatever comes after this call in an ORDER BY will be automatically removed and flagged as a warning.

Nested ORDER BY Issues and Fixes

It is incredible how many vendors dismissed our specific needs to use, in some cases, for good reasons (like for slicing, with Skip-and-Keep), an ORDER BY in a subquery. For many platforms with this limitation, we discovered and provided fixes we hope you will appreciate (because they let your query run!):

  • Microsoft SQL Server, Azure, SQL CE – ORDER BY in subquery throws an error without our fix. Which is adding a TOP maxn (where maxn is a very big integer, the maximum allowed) to the subquery.
  • MariaDB – ORDER BY in subquery is simply ignored without our fix. Which is adding a LIMIT maxn (where maxn is a very big integer, the maximum allowed) to the subquery. (It’s interesting MySQL doesn’t need such a fix.)
  • SQL Anywhere – ORDER BY in subquery is simply ignored without our fix. Which is adding a TOP maxn (where maxn is a very big integer, the maximum allowed) to the subquery.
  • Microsoft Access – TOP with ORDER BY may return wrong results, because TOP is executed before ORDER BY. Our fix is wrapping up the subquery without TOP into a SELECT TOP n * FROM (SELECT … ORDER BY …), whenever we detect a TOP and an ORDER BY in the same query.
  • Firebird – FIRST with ORDER BY may throw an error, possibly because of the same reason. Our fix is also wrapping up the subquery without FIRST into a SELECT FIRST n * FROM (SELECT … ORDER BY …), whenever we detect a FIRST and an ORDER BY in the same query.
  • Sybase/SAP ASE and Ingres – ORDER BY in subquery throws an error. We’re still looking for a workaround. Until then, please adjust your queries accordingly on reuse.