6.2.4 The #:order clause

The #:order clause specifies the order in which query results should be returned.

The clause is a list of order terms, each of which can be one of the following:

(order expr direction)

Sort by expr, where expr is an attribute or expression. direction can be the literal 'asc (ascending order):

  (sql (select #:from  person
               #:order ((order person.age 'asc))))

the literal 'desc (descending order):

  (sql (select #:from  person
               #:order ((order person.age 'desc))))

or an unquoted Scheme expression evaluating to 'asc or 'desc:

  (sql (select #:from  person
               #:order ((order person.age
                               ,(string->symbol "asc")))))
(asc expr)

Shorthand for (order expr 'asc).

(desc expr)

Shorthand for (order expr 'desc).

When multiple terms are specified, they are treated in descending order of precedence. For example, the expression:

  (find-all
   (sql (select #:from  person
                #:order ((desc person.age)
                         (asc person.surname)))))

would return a (listof person) sorted first by descending age, then by alphabetical surname.

Note that the SQL-97 standard dictates that expressions and attributes in the #:order clause must appear in the #:what clause. Expressions must be aliased so the DBMS can match the two clauses up.

For example:

  (let-alias ([x (sql (+ person.age person.shoe-size))])
    (sql (select #:what  x
                 #:from  person
                 #:order ((asc x)))))