Order By (or Order)

The order statement sorts an array of nid, rid, or JSON objects and returns the same array sorted. The keyword by can optionally follow order.

Example:

(select Customer order 'First name').'first name'

Sorting Texts

Sorting texts is done based on ASCII code. Uppercase and lowercase letters have different values, as do accented characters.
For alphabetical sorting, it is better to use the upper() function to convert text to uppercase before sorting:

(select Customer order upper('First name')).text('first name')

Note: The upper() function does not remove accents; it only converts characters to uppercase. Therefore, this approach only partially works for alphabetical sorting.
To fully handle accents, you need a custom function to remove them:

Accent Removal Function:

function SansAccent(S : text) do
  S := replacex(S, "Ç|Ć", "g", "C");
  S := replacex(S, "ç", "g", "c");
  S := replacex(S, "è|é|ê|ë", "g", "e");
  S := replacex(S, "È|É|Ê|Ë", "g", "E");
  S := replacex(S, "à|á|â|ã|ä|å", "g", "a");
  S := replacex(S, "@|À|Á|Â|Ã|Ä|Å", "g", "A");
  S := replacex(S, "ì|í|î|ï", "g", "i");
  S := replacex(S, "Ì|Í|Î|Ï", "g", "I");
  S := replacex(S, "ð|ò|ó|ô|õ|ö", "g", "o");
  S := replacex(S, "Ò|Ó|Ô|Õ|Ö", "g", "O");
  S := replacex(S, "ù|ú|û|ü", "g", "u");
  S := replacex(S, "Ù|Ú|Û|Ü", "g", "U");
  S := replacex(S, "ý|ÿ", "g", "y");
  S := replacex(S, "Ý", "g", "Y");
  S
end;

(select Customer order SansAccent(upper('First name'))).text('first name')

Ascending and Descending Sorting

The default sorting order is ascending (from smallest to largest).
To sort in descending order, the approach depends on the type of value:

For Numerical Values:

You can use the minus sign (-):

(select Invoice order -'Invoice Date').text('Invoice Date')

For Texts:

There is no built-in function to reverse a text. You must create one. Below is an example that handles all 256 ASCII characters, iterating through each character and reversing its value:

Reverse Text Function:

function reverseAscii(t : text) do
  var ascii := urlDecode("%C3%BF%C3%BE%C3%BD%C3%BC%C3%BB%C3%BA%C3%B9%C3%B8%C3%B7%C3%B6%C3%B5%C3%B4%C3%B3%C3%B2%C3%B1%C3%B0%C3%AF%C3%AE%C3%AD%C3%AC%C3%AB%C3%AA%C3%A9%C3%A8%C3%A7%C3%A6%C3%A5%C3%A4%C3%A3%C3%A2%C3%A1%C3%A0%C3%9F%C3%9E%C3%9D%C3%9C%C3%9B%C3%9A%C3%99%C3%98%C3%97%C3%96%C3%95%C3%94%C3%93%C3%92%C3%91%C3%90%C3%8F%C3%8E%C3%8D%C3%8C%C3%8B%C3%8A%C3%89%C3%88%C3%87%C3%86%C3%85%C3%84%C3%83%C3%82%C3%81%C3%80%C2%BF%C2%BE%C2%BD%C2%BC%C2%BB%C2%BA%C2%B9%C2%B8%C2%B7%C2%B6%C2%B5%C2%B4%C2%B3%C2%B2%C2%B1%C2%B0%C2%AF%C2%AE%C2%AD%C2%AC%C2%AB%C2%AA%C2%A9%C2%A8%C2%A7%C2%A6%C2%A5%C2%A4%C2%A3%C2%A2%C2%A1%C2%A0%C2%9F%C2%9E%C2%9D%C2%9C%C2%9B%C2%9A%C2%99%C2%98%C2%97%C2%96%C2%95%C2%94%C2%93%C2%92%C2%91%C2%90%C2%8F%C2%8E%C2%8D%C2%8C%C2%8B%C2%8A%C2%89%C2%88%C2%87%C2%86%C2%85%C2%84%C2%83%C2%82%C2%81%C2%80%7F~%7D%7C%7Bzyxwvutsrqponmlkjihgfedcba%60_%5E%5D%5C%5BZYXWVUTSRQPONMLKJIHGFEDCBA@?%3E=%3C;:9876543210/.-,+*)('&%25$#%22!%20%1F%1E%1D%1C%1B%1A%19%18%17%16%15%14%13%12%11%10%0F%0E%0D%0C%0B%0A%09%08%07%06%05%04%03%02%01%00");
  join(
    for i in t do
      item(ascii, 255 - index(ascii, i))
    end, "")
end;

(select Customer order reverseAscii(SansAccent(upper('First Name')))).'First Name'

Multi-Field Sorting

To sort a table by multiple fields, chain multiple order by statements. For example, the following sorts a list first by last name, and then by first name for identical last names:

(select Customer order upper(SansAccent('First Name')) order upper(SansAccent('Last Name'))).
---{'First Name'} {'Last Name'}---;

Note: order by statements go from the least significant to the most significant field.

Sorting JSON Arrays

It is also possible to sort JSON arrays:

Example:

var myArray := [ {'prénom' : "Jan"}, {'prénom' : "Fred"}, {'prénom' : "Jacques"}];
myArray order by 'prénom';

You can also shorten this code as follows:

([ {'prénom' : "Jan"}, {'prénom' : "Fred"}, {'prénom' : "Jacques"}] order by 'prénom');

Note: The compiler does not verify the existence of the key used for sorting. If the key does not exist, the array will remain unsorted.