Skip to content

Lets you define a custom sorting that is not alphabetical

Contributed via Discord

Sort a table with a custom sort order

Basic

TABLE rows.file.link
FROM "10 Example Data/food"
WHERE recipe-type
GROUP BY recipe-type
SORT choice(recipe-type = "vegetarian", "1", choice(recipe-type = "meat", "2","3")) ASC

Variants

If you have a lot of values to sort

Query Explanation

Wondering what's happening here? Let's have a look. First, we're calling default(customsort, "99") - that means "If the custom sort doesn't return anything, use 99 as the sort value" - and since we have less than 99 sort items, it'll put it to the very end (and sort all with the same value alphabetically).
The magic happens here:
((x) => something)(G)
We're calling ((x) => something)(G), meaning we define a lambda function and put in G (our flattened Genre) as a parameter. G becomes x inside the function.
{ "Drama":1, "Crime":2, "Horror": 3, "Comedy": 4, "Thriller": 5, "Science-Fiction": 6 }
is an object that maps our genres to a numeric value that's in the end used to define the sorting. This is what puts "Drama" first and "Crime" second. By calling [x] at the very end, we "search" for the genre we pass into the lambda inside the function - returning its numeric value if available or nothing if we did not define it. In case of the latter, we'll get back 99 due to the default on top.

With a flattened field

TABLE WITHOUT ID key AS "genre", rows.file.link AS "shows"
FROM "10 Example Data/shows"
FLATTEN Genre as G
GROUP BY G
SORT default(((x) => {
"Drama":1, 
"Crime":2,
"Horror": 3,
"Comedy": 4,
"Thriller": 5,
"Science-Fiction": 6
}[x])(G), "99") ASC

With no flattened field

TABLE Show_status
FROM "10 Example Data/shows"
SORT default(((x) => {
"Running":1,
"To Be Determined": 2,
"Ended": 3
}[x])(show_status), "99") ASC