Order by

The Order by clause sorts the binding-tuple stream by one or more keys. Ascending is the default. Nulls sort last in ascending order and first in descending order.

Syntax

Railroad

orderbyorder-key,order-key

BNF

order-clause ::= "order" "by" order-key ( "," order-key )*

order-key ::= expr [ ( "asc" | "desc" ) ]

Rules

  1. Default sort direction is asc. (phase: after where / group, before limit and select)
  2. null sorts last in asc and first in desc. (phase: before limit)
  3. Numbers order by numeric value; 1 and 1.0 compare equal. (phase: before limit)
  4. Sort stability is not guaranteed. (phase: before limit)

Examples

Minimal

Order By

Order by sorts ascending by default.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2});

select t.x from T as t order by t.x;

Result

[
  1,
  2,
  3
]

Explicit Asc

Explicit asc matches the default.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2});

select t.x from T as t order by t.x asc;

Result

[
  1,
  2,
  3
]

Order By

Order by reorders whole rows under select *.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2});

select * from T as t order by t.x;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Null Sorts

Null sorts after all values in ascending order.

SQL

create table T;

insert into T ({"x": 2}, {"x": null}, {"x": 1});

select t.x from T as t order by t.x;

Result

[
  1,
  2,
  null
]

Ints And

Ints and floats interleave by numeric value.

SQL

create table T;

insert into T ({"x": 2}, {"x": 1.5}, {"x": 1});

select t.x from T as t order by t.x;

Result

[
  1,
  1.5,
  2
]

Strings Sort Lexicographically

Strings sort lexicographically.

SQL

create table T;

insert into T ({"s": "banana"}, {"s": "apple"}, {"s": "cherry"});

select t.s from T as t order by t.s;

Result

[
  "apple",
  "banana",
  "cherry"
]

Order By

Order by then limit yields the top N.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2}, {"x": 5}, {"x": 4});

select t.x from T as t order by t.x desc limit 2;

Result

[
  5,
  4
]

Order Sorts

Order sorts the post-where stream.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2}, {"x": 4});

select t.x from T as t where t.x > 1 order by t.x;

Result

[
  2,
  3,
  4
]

Compound

Desc Sorts Descending

Desc sorts descending.

SQL

create table T;

insert into T ({"x": 3}, {"x": 1}, {"x": 2});

select t.x from T as t order by t.x desc;

Result

[
  3,
  2,
  1
]

Multiple Keys

Multiple keys sort left-to-right with per-key direction.

SQL

create table T;

insert into T ({"a": 1, "b": 2}, {"a": 1, "b": 1}, {"a": 2, "b": 5});

select * from T as t order by t.a, t.b desc;

Result

[
  { "a": 1, "b": 2 },
  { "a": 1, "b": 1 },
  { "a": 2, "b": 5 }
]

Null Sorts

Null sorts before all values in descending order.

SQL

create table T;

insert into T ({"x": 2}, {"x": null}, {"x": 1});

select t.x from T as t order by t.x desc;

Result

[
  null,
  2,
  1
]

Mixed Types

Mixed types sort by the same total order as the comparison operators (bool < number < string < null).

SQL

create table T;

insert into T ({"x": "s"}, {"x": null}, {"x": 1}, {"x": true});

select t.x from T as t order by t.x;

Result

[
  true,
  1,
  "s",
  null
]

Order By

Order by sorts the cross product of two sources.

SQL

create table T;

create table S;

insert into T ({"x": 2}, {"x": 1});

insert into S ({"y": 9});

select * from T as t, S as s order by t.x;

Result

[
  { "x": 1, "y": 9 },
  { "x": 2, "y": 9 }
]

See also