0

I use SurrealDB as my go-to database for just about everything nowadays--it's just so damn powerful.
Today, I have encountered a minor issue: While implementing it as a datastore as part of a 3rd-party framework, I need to sort the output by a field in ascending order, with NULLs and NONEs sorted last.

When I run SELECT * FROM Table ORDER field ASC; in SurrealDB's SQL REPL, I get something like this back:

[
        {
                field: NULL,
                id: Table:mzg9su37s8l1rwjpgu47
        },
        {
                field: 1,
                id: Table:dy6e1uwbrobzuq5rtm9k
        },
        {
                field: 2,
                id: Table:cn93fh6qtn1wo5dmuoeu
        }
]

The NULL comes first, not last. I've skimmed through the docs page for the SELECT statement, and I didn't find anything. If all else fails--and as a hopefully temporary solution--I can just run two queries--one to get the non-NULLs, one for the NULLs--back-to-back, and just concatenate their results in the backend.

M1N3R
  • 33
  • 4

1 Answers1

0

As per the sorting order shown here, it does not seem like you can currently sort NULLs and NONEs last.

However, you can try to chain your queries using union as shown below. Remember to replace "tbl" with your table name.

RETURN array::union(
    (SELECT * FROM tbl WHERE field!=null ORDER field ASC),
    (SELECT * FROM tbl WHERE field=null)
)

Hope this helps!

naisofly
  • 104
  • 1
  • 4