Entries in desc (2)

What is q's equivalent to sql's ORDER BY?

xasc and xdesc. Consider the following table:

q)table: ([] x: 10 ? "abc"; y: 10 ? til 10)
q)table
x y
---
a 7
a 7
b 1
b 9
a 1
a 0
c 8
b 8
c 3
c 1
q)

To sort by column x, instead of "ORDER BY x", we write `x xasc table:

q)`x xasc table // sort by column x
x y
---
a 7
a 7
a 1
a 0
b 1
b 9
b 8
c 8
c 3
c 1
q)

The application of xdesc is similar.

In addition, to sort by multiple columns, instead of "ORDER BY y, x" or "ORDER BY y, x DESC", we pass the list of column names as the left argument to xasc or xdesc, respectively. For example,

q)`y`x xdesc select from table where y > 5
x y
---
b 9
c 8
b 8
a 7
a 7
q)

Don't confuse xasc and xdesc with asc and desc, which operate on a vector instead of a table. Read more about sorting vectors in this related faq.

How do I sort?

Short answer: asc and desc:

q)asc 3 5 2 0 1
`s#0 1 2 3 5
q)desc 3 5 2 0 1
5 3 2 1 0
q)

Notice that, in the case of asc, q attaches the `s attribute to the result. (There is no corresponding attribute for a descending sort.)

As an aside, you may be surprised to discover that calling asc on an attribute-free list that happens to be in sorted order will have the `s attribute applied to it as a side effect:

q)list: til 1000
q)attr list
`
q)asc list
`s#0 1 2 3 4 5 6 7 8 9 10 ..
q)list
`s#0 1 2 3 4 5 6 7 8 9 10 ..
q)

Also take a look at iasc and idesc.

Finally, to sort a table instead of a list, see xasc and xdesc. Read more about sorting tables from this faq