How to Sort Query Results
This page shows you how to sort query results and tables in KDB-X.
Overview
Sort data in ascending order
To sort an in-memory table in ascending order, use the asc keyword.
When asc is applied to a table, it returns the table sorted by the first non-key column, whereby:
- the sorted attribute is set on that column if there is only one non-key column
- otherwise, the parted attribute is set on that column
Simple table example:
q)show t1:([]a:3 4 1;b:`a`d`s) / unkeyed table, 2 non-key cols
a b
---
3 a
4 d
1 s
q)asc t1
a b
---
1 s
3 a
4 d
q)meta asc t1 / parted attribute set
c| t f a
-| -----
a| j p
b| s
q)meta asc ([]a:3 4 1) / unkeyed table, one non-key column
c| t f a / sorted attribute set
-| -----
a| j s
q)/ to persist sort, you must re-define the table using colon
q)asc `t1
'rank
[0] asc `t1
^
q)t1:asc t1
q)t1
a b
---
1 s
3 a
4 d
Keyed table example:
q)show t2:([c1:`a`b`c]c2:3 1 2;c3:010b) / keyed table, 2 non-key cols
c1| c2 c3
--| -----
a | 3 0
b | 1 1
c | 2 0
q)asc t2
c1| c2 c3
--| -----
b | 1 1
c | 2 0
a | 3 0
q)meta asc t2 / parted attribute set
c | t f a
--| -----
c1| s
c2| j p
c3| b
q)meta asc ([c1:`a`b]c2:2 1) / keyed table, one non-key column
c | t f a / sorted attribute set
--| -----
c1| s
c2| j s
To sort in ascending order on a specific column or multiple columns, use the xasc keyword.
Example:
q)show t:([]a:3 3 3 2 2 2 1 1 1;b:2 2 1 2 2 1 2 2 1;c:3 2 1 3 2 1 3 2 1)
a b c
-----
3 2 3
3 2 2
3 1 1
2 2 3
2 2 2
2 1 1
1 2 3
1 2 2
1 1 1
q)`b xasc t / sort on column 'b'
a b c
-----
3 1 1
2 1 1
1 1 1
3 2 3
3 2 2
2 2 3
2 2 2
1 2 3
1 2 2
q)`a`b xasc t / sort on multiple columns
a b c
-----
1 1 1
1 2 3
1 2 2
2 1 1
2 2 3
2 2 2
3 1 1
3 2 3
3 2 2
Sort data in descending order
To sort an in-memory a table in descending order, use the desc keyword.
When desc is applied to a table, it returns the table sorted by the first non-key column, with no attributes set.
Simple table example:
q)show t3:([]a:3 4 1;b:`a`d`s) / unkeyed table, 2 non-key cols
a b
---
3 a
4 d
1 s
q)desc t3
a b
---
4 d
3 a
1 s
q)meta desc t3 / no attributes set
c| t f a
-| -----
a| j
b| s
q)meta desc ([]a:3 4 1) / unkeyed table, one non-key column
c| t f a / no attributes set
-| -----
a| j
Keyed table example:
q)show t4:([c1:`a`b`c]c2:3 1 2;c3:010b) / keyed table, 2 non-key cols
c1| c2 c3
--| -----
a | 3 0
b | 1 1
c | 2 0
q)desc t4
c1| c2 c3
--| -----
a | 3 0
c | 2 0
b | 1 1
q)meta desc t4 / no attributes set
c | t f a
--| -----
c1| s
c2| j
c3| b
q)meta desc ([c1:`a`b]c2:2 1) / keyed table, one non-key column
c | t f a / no attributes set
--| -----
c1| s
c2| j
To sort in descending order on a specific column or multiple columns, use the xdesc keyword.
Sort data on disk
xasc can sort tables on disk directly, without loading them into memory. xasc will automatically apply the sorted attribute.
xdesc can be used in the same way, but the sorted attribute will not be applied.
q)show t:([]b:`s`g`a`s`a;c:30 10 43 13 24;g:til 5)
b c g
------
s 30 0
g 10 1
a 43 2
s 13 3
a 24 4
q)`:db/t/ set .Q.en[`:db]t / write splayed table
`:db/t/
q)`c xasc `:db/t / sort table on disk by column c
`:db/t
q)t / in-memory table remains unsorted
b c g
------
s 30 0
g 10 1
a 43 2
s 13 3
a 24 4
q)\l db/t / load table from disk
`t
q)t / table on disk is sorted
b c g
------
g 10 1
s 13 3
a 24 4
s 30 0
a 43 2
q)meta t / sorted attribute set
c| t f a
-| -----
b| s
c| j s
g| j
Note
Duplicate column names in a table can cause sorts to return unpredictable results.
Summary
In this guide, you:
- Learned how to sort in-memory tables in ascending and descending order
- Learned how to sort data on disk
You now have the essential skills to sort query results and tables in KDB-X.