Add 'order' clause

[julianhyde]

Add an order sub-clause to the from expression, to allow sorting.

Failed attempt - follow SQL

The obvious syntax would follow SQL, which evaluates ORDER BY after SELECT:

from e in hr.emps
where e.deptno = 10
yield {e.empno, compensation = e.sal + e.commission}
order compensation

There are some problems with this. First, compensation isn't in scope after yield (neither is e); after yield, the variables declared in from are gone, and all we have left are the elements of the result.

Also, we might want to sort on a field (or expression) that we do not wish to emit.

Better: Put the order before the yield

This is what XQuery's FLWOR does - note O (order) before R (return) - and XQuery is generally a good example to follow.

The query is now

from e in hr.emps
where e.deptno = 10
order e.sal + e.commission
yield {e.empno, compensation = e.sal + e.commission}

We have to write the expression for compensation twice, but it's not much of a hardship. With #11 we would be able to write

from e in hr.emps,
    compensation = e.sal + e.commission
where e.deptno = 10
order compensation
yield {e.empno, compensation}

Now order is before yield, we can omit compensation from yield if we choose:

from e in hr.emps
where e.deptno = 10
order e.sal + e.commission
yield {e.empno}

As always with from, we can omit yield. (yield e is implicit; if there are multiple variables, say x, y, z, then yield {x, y, z} is implicit.)

from e in hr.emps
where e.deptno = 10
order e.sal + e.commission

Composite and descending keys

Comma to separate keys, and desc to reverse order seem to be appropriate:

from e in hr.emps
order e.deptno, e.birthdate desc
yield {e.empno, e.name}

Ordering function

Sometimes it is tricky to generate a value to compare on, and it is easier to use an ordering function (what Java would call a 'comparator').

The Jooq blog has an example of sorting versioned file names into the following order using a hybrid of alphabetical and numeric comparisons:

• C:\temp\version-1.sql
• C:\temp\version-2.sql
• C:\temp\version-10.sql
• C:\temp\version-10.1.sql
• C:\temp\version-21.sql

It might be possible to write a function to extract a sort key (like ["C:\temp\version-", 1, ".sql"]) but it would not be easy. So, I propose a infix operator using that indicates sorting by passing them pairwise to a ordering function that returns an order:

let
  fun compareFileNames f1 f2 =
    if ... then
      order.GREATER
   else
     order.LESS
in
  from f in files
    order f.name using compareFileNames desc, f.accessTime
end

The syntax is <expression> using <function>, and you can combine with desc and use in a composite order.

order (and yield) after group

A from expression that contains a group implicitly yields a record consisting of the group expressions and compute expressions. For example,

from e in emps
  group e.deptno, e.job compute sum of e.sal as sumSal

returns a list of {deptno, job, sumSal} records. I propose that you can use those fields (as if they were variables) in an order or yield sub-clause. (The variables in the from clause, e in this case, are no longer available.) For example,

from e in emps
  group e.deptno, e.job compute sum of e.sal as sumSal
  order sumSal desc
  yield {deptno, job = substring (job, 0, 2)}

[julianhyde]

It's probably better if the function comes before the argument. So rather than

from f in files
    order f.name using compareFileNames desc, f.accessTime

we'd have

from f in files
    order using compareFileNames of f.name desc, f.accessTime

Of course you can have a fn after using, or any function value, not just a function name.

Same goes for the compute clause.

[julianhyde]

Fixed in 8eed7be.