Sort records

order by

Use order by to sort an array of records by a specific field. This is useful when sorting an array first before processing it further.

When you use order by in a View layout item, it is first sorted by a (selected) column header and second by the value specified after order by.

order by works best when you sort records by a number.

Example

(select Invoices) order by Total

Result: The entries of the Invoices table are sorted by the Total field (from small to large).

Example

Restrict the selection of records with where or brackets [...].

The value after order by does not necessarily have to be a field name, it can also be manipulated by functions, for example.

(select Invoices where Date = today()) order by number(substr('Invoice no.', 3))

Result: The entries with today's date of the Invoices table are sorted by the number in the Invoice no. text field.

3 refers to the position of the number within the invoice no., which in our case, for example, starts with the 4th position: NO-12574 (0=N, 1= O, 2=-, 3= 1 (the first number)).

Order by works best when you sort records by a number.

Tip: Sorting strings

Be careful when sorting strings. Strings are not sorted alphabetically, but according to the index of the characters. So first all upper case letters are sorted alphabetically, then the lower case letters.

Explanation

ABcD is thus sorted to ABDc. To get the desired result, simply unify the values by upper() or lower(). Then all initial letters are first set to upper case or lower case respectively.

Result: ABcD

Alternative: Use the Ninox function sort() instead of order by.

Example

You have a table Example for order by with a First name field. It contains the following records: Aaron, Eddi, conrad, beate, Dahlia, and Fatima. Insert the following script into a formula field.

concat(((select 'Example for order by') order by 'First name').'First name')

Result: Aaron, Dahlia, Eddi, Fatima, beate, conrad

This is not the desired alphabetical sorting...

We add upper(), which sets all entries to upper case for sorting.

concat(((select 'Example for order by') order by upper('First name')).'First name')

Result: Aaron, beate, conrad, Dahlia, Eddi, Fatima 🎉

If you only need the list of names and not the records themselves, we recommend using sort():

concat(sort((select 'Example for order by').'First name'))

Result: Aaron, beate, conrad, Dahlia, Eddi, Fatima