How to select data¶
This page shows you how to query tables with the qSQL select statement, through examples on in-memory tables, and then explains what's different when the data is on disk.
select returns a table. For the full syntax, see the select reference.
You can also process a table with basic q constructs, as described in Tables. For example, to get the average trade size in the first minute after exchange open, index the table by column name, index the result by a list of longs, and then apply avg:
q)avg trade[`size] where trade[`time] within 0D09:30 0D09:31
50.29787
The equivalent select returns a table instead of an atom:
q)select avg size from trade where time within 0D09:30 0D09:31
size
--------
50.29787
The basic constructs are fine for simple lookups, but select is usually clearer (there are no repeated table names) and is more expressive. To get the average trade size for each stock with basic constructs, you split the table by stock and loop over the parts. With select, you add a by phrase:
q)select avgSize: avg size by sym from trade
sym | avgSize
------| --------
AAPL | 48.91228
AGEN | 53.28571
AIG | 52.16949
..
select works the same way on in-memory tables, on a table mapped from disk, and on a complete database loaded from disk. You usually don't need to change your queries when your data no longer fits into memory and you persist it to disk. See Query on-disk tables for the few differences.
Example data¶
The examples query US equity trades and quotes generated by the datagen module. To create the tables in memory:
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; master; exnames): getInMemoryTables ([tbls: `trade])
Select all columns¶
With no column list, select returns all columns:
q)select from trade where sym=`AAPL
sym time price size stop cond ex
--------------------------------------------------
AAPL 0D09:30:22.699448654 255.6 44 0 P S
AAPL 0D09:32:29.061196651 256.24 90 0 R W
AAPL 0D09:33:26.493750743 256.57 37 0 K Q
..
Select specific columns¶
List the columns you need, separated by commas:
q)select sym, time, price from trade where sym=`AAPL
sym time price
--------------------------------
AAPL 0D09:30:22.699448654 255.6
AAPL 0D09:32:29.061196651 256.24
AAPL 0D09:33:26.493750743 256.57
..
Every table also has the virtual column i, the index of each row. It isn't stored, but you can select it and filter on it. In the result, i appears as column x unless you name it:
q)select idx: i, sym, price from trade where sym=`AAPL, i<500
idx sym price
---------------
29 AAPL 255.6
81 AAPL 256.24
100 AAPL 256.57
190 AAPL 258.16
258 AAPL 258.35
270 AAPL 258.57
397 AAPL 260.3
i is the row's index in the table being queried, not in the result. The first AAPL trade is row 29 of trade.
Select distinct rows¶
Use select distinct to remove duplicate rows from the result, for example to list the exchanges that AAPL traded on in the first hour:
q)select distinct ex from trade where sym=`AAPL, time within 0D09:30 0D10:30
ex
--
S
W
Q
Y
D
N
C
distinct applies to whole rows, so with several columns, each combination of values appears once. To count the distinct values in each group, use the distinct keyword inside an aggregation:
q)select n:count distinct ex by sym from trade where sym in `AAPL`MSFT
sym | n
----| --
AAPL| 12
MSFT| 12
Derive new columns¶
A column in the select phrase can be any q expression, and you name the result with name:. Expressions operate on whole columns at once, so price*size multiplies two vectors element by element:
q)select time, price, size, notional:price*size from trade where sym=`AAPL
time price size notional
-----------------------------------------
0D09:30:22.699448654 255.6 44 11246.4
0D09:32:29.061196651 256.24 90 23061.6
0D09:33:26.493750743 256.57 37 9493.09
..
Because expressions see the whole column, you can also refer to other rows. Here, prev shifts the column by one row to calculate the price change between consecutive trades:
q)select time, price, change:price-prev price from trade where sym=`AAPL
time price change
----------------------------------
0D09:30:22.699448654 255.6
0D09:32:29.061196651 256.24 0.64
0D09:33:26.493750743 256.57 0.33
...
If you don't name a column, q names it after the leftmost name in its expression, or x if there is none. A name that is already in use gets the suffix 1, 2, and so on:
q)select sym, price, 2*price, price*size, price from trade where sym=`AAPL
sym price x price1 price2
---------------------------------
AAPL 255.6 511.2 11246.4 255.6
AAPL 256.24 512.48 23061.6 256.24
AAPL 256.57 513.14 9493.09 256.57
...
q doesn't rename a column that clashes with a by column. Instead, the query signals an error, and you need to name one of them:
q)select sym by sym from trade
'dup names for cols/groups sym
Each expression in the select phrase is evaluated against the input table. A computed column isn't available to another expression in the same select phrase, or to the where clause:
q)select time, notional:price*size from trade where notional>20000
'notional
Repeat the expression in the where clause, or calculate the column in an inner query and use it in an outer one:
q)select time, notional from (select time, notional:price*size from trade) where notional>20000
Put the join operator in parentheses¶
A common mistake
In a qSQL statement, a comma separates the columns of the select phrase, the constraints of the where clause, and the columns of the by phrase. q reads every comma at the top level of a phrase as a separator, never as the join operator ,. To use join, put the expression that contains it in parentheses.
For example, to label each trade with its stock and exchange, join the two columns item by item with ,', then join each pair into one string with sv. Without parentheses, q reads string sym,'ex as two columns, string sym and 'ex:
q)select time, venue:"," sv' string sym,'ex from trade where sym=`AAPL
'type
q)select time, venue:("," sv' string sym,'ex) from trade where sym=`AAPL
time venue
-----------------------------
0D09:30:22.699448654 "AAPL,S"
0D09:32:29.061196651 "AAPL,W"
0D09:33:26.493750743 "AAPL,Q"
...
The same happens in the where clause. A typical case is building a list of values from a variable and a constant. Without parentheses, q reads sym in s,`MSFT as two constraints, sym in s and `MSFT:
q)s:`AAPL
q)select count i by sym from trade where sym in s,`MSFT
'type
q)select count i by sym from trade where sym in (s,`MSFT)
sym | x
----| --
AAPL| 57
MSFT| 42
In the by phrase, by sym,ex groups by two columns, and by (sym,'ex) groups by one column of pairs.
Choose values with a condition¶
Cond ($[…]) chooses between two expressions with one boolean atom, so it doesn't work on a column, and q signals rank when you use it in a query. Use the vector conditional ?[…] instead, which chooses item by item:
q)select time, price, size, lot:$[size>50;`large;`small] from trade where sym=`AAPL
'rank
q)select time, price, size, lot:?[size>50;`large;`small] from trade where sym=`AAPL
time price size lot
--------------------------------------
0D09:30:22.699448654 255.6 44 small
0D09:32:29.061196651 256.24 90 large
0D09:33:26.493750743 256.57 37 small
...
To use Cond on an atom value, for example a function parameter, put it inside a lambda, as in where sym={$[x;`AAPL;`MSFT]} flag.
Apply functions to columns¶
Most q operators work on lists, so you can write price*size and q multiplies the two columns element by element. You don't need explicit iteration such as price*'size.
A custom function that handles only atoms is different. Consider a table with a column of strings, where each string is a comma-separated list such as "foo,bar":
q)t:([] id:1 2 3; tags:("foo,bar";"baz,qux,quux";"abc"))
The function {first "," vs x} takes one string, splits it at the commas with vs, and returns the first part. Applied to the whole column, vs receives a list of strings instead of a single string, and fails:
q)select id, first "," vs tags from t
'type
Use each to apply the function to one string at a time:
q)select id, tag:{first "," vs x} each tags from t
id tag
--------
1 "foo"
2 "baz"
3 "abc"
Filter rows¶
How the where clause works¶
Each constraint in the where clause is an expression that returns a boolean vector, one value per row. select keeps the rows where the value is 1b. For example, sym in `AAPL`MSFT evaluated on the first five rows of master gives:
q)exec 5#sym in `AAPL`MSFT from master
10000b
Any expression that returns a boolean vector of the right length works as a constraint, for example a range check with within:
q)select time, price from trade where sym=`AAPL, price within 270 272
time price
---------------------------
0D12:13:45.455608529 270.13
0D12:14:18.495653330 270.13
0D12:14:43.491827357 270.13
0D12:24:58.838196835 271.02
Successive filters¶
Separate constraints with commas to apply them one after another. q evaluates each constraint only on the rows that passed the previous ones:
q)select time, price, size from trade where sym=`AAPL, size>80, ex=`D
time price size
--------------------------------
0D09:47:23.248078876 258.35 84
0D10:45:02.804768202 263.21 83
Here q evaluates sym=`AAPL on all 2,619 rows and finds 57. It then evaluates size>80 on only those 57 rows and keeps 10, and ex=`D on only those 10.
The comma isn't the same as and. where (sym=`AAPL) and (size>80) and ex=`D returns the same rows, but q evaluates every operand on every row of the table before combining them. Commas do less work whenever earlier constraints remove rows.
A constraint on i always refers to the row's index in the table, not in the rows that passed earlier constraints. The first AAPL trade is row 29, so this query returns nothing:
q)select from trade where sym=`AAPL, i<3
sym time price size stop cond ex
--------------------------------
To get the first rows of a filtered result, limit the result instead.
Filter strings with the like keyword¶
Use like to match a string or symbol column against a pattern. * matches any sequence of characters, ? matches one character, and […] matches one of a set of characters:
q)select sym, description from master where description like "*Inc."
sym description
-----------------------------------------
AAPL "Apple Inc."
AGEN "Agenus Inc."
AIG "American International Group, Inc."
AMTM "Amentum Holdings, Inc."
CUZ "Cousins Properties Inc."
DELL "Dell Technologies Inc."
DOW "Dow Inc."
GOGO "Gogo Inc."
HPQ "HP Inc."
PEP "PepsiCo, Inc."
PRU "Prudential Financial, Inc."
SNAP "Snap Inc."
T "AT&T Inc."
like is case-sensitive. For a case-insensitive match, convert the column with lower and use a lowercase pattern:
q)select sym, description from master where lower[description] like "*bank*"
sym description
---------------------------------
BAC "Bank of America Corporation"
Filter within groups with the fby keyword¶
To filter rows by comparing them with an aggregate of their group, use fby. (agg;col) fby grp calculates agg col for each group of grp and returns the result for each row's group. For example, to find when each stock traded at its highest price:
q)select sym, time, price from trade where sym in `AAPL`MSFT`NVDA, price=(max;price) fby sym
sym time price
--------------------------------
NVDA 0D15:34:32.976774435 195.89
NVDA 0D15:42:08.816737168 195.89
AAPL 0D15:59:36.297840840 285.48
MSFT 0D15:59:50.325049624 562.95
NVDA traded at its highest price twice, so it appears twice. Or to count, for each stock, the trades above its average price:
q)select count i by sym from trade where sym in `AAPL`MSFT, price>(avg;price) fby sym
sym | x
----| --
AAPL| 28
MSFT| 21
To group by several columns, pass them as a table. For example, to find the highest-priced AAPL trade on each of two exchanges:
q)select sym, ex, time, price from trade where sym=`AAPL, ex in `N`Q, price=(max;price) fby ([]sym;ex)
sym ex time price
-----------------------------------
AAPL Q 0D14:55:11.312317265 279.49
AAPL N 0D15:33:16.008235746 282.17
Filter using another table¶
A constraint can use the result of another query. Use exec to return a list of values from the other table, and in to keep the rows that match. For example, to calculate the volume-weighted average price of the companies whose description ends in Inc.:
q)select vwap:size wavg price by sym from trade where sym in exec sym from master where description like "*Inc."
sym | vwap
----| --------
AAPL| 270.8899
AGEN| 4.225693
AIG | 83.81784
AMTM| 26.39825
CUZ | 29.94107
DELL| 154.0652
DOW | 23.86345
GOGO| 8.690719
HPQ | 27.63718
PEP | 148.4952
PRU | 107.6741
SNAP| 7.900206
T | 28.09864
q evaluates the exec query once, then uses its result as the list in sym in ….
Foreign keys
If a column is a foreign key to another table, you can refer to the other table's columns with dot notation instead, for example sym.description, without a subquery or a join. The example tables have no foreign keys. See Query across the key.
Drop rows with nulls¶
Use null to find missing values, and not null to drop the rows that have them. In comparisons, a null is less than any other value of its type, so a constraint such as change<=0 also keeps the first row, where change is null:
q)r:select time, price, change:price-prev price from trade where sym=`AAPL
q)select from r where change<=0
time price change
----------------------------------
0D09:30:22.699448654 255.6
0D10:45:02.804768202 263.21 0
0D10:45:35.175903722 263.21 0
...
Put not null first to exclude it:
q)select from r where not null change, change<=0
time price change
----------------------------------
0D10:45:02.804768202 263.21 0
0D10:45:35.175903722 263.21 0
0D11:31:19.246905106 267.16 0
...
Group rows¶
Add a by phrase to group rows. The result is a keyed table with one row for each group, and the aggregations in the select phrase are calculated for each group:
q)select n:count i, vwap:size wavg price, high:max price by sym from trade where sym in `AAPL`MSFT`NVDA
sym | n vwap high
----| ------------------
AAPL| 57 270.8899 285.48
MSFT| 42 539.7139 562.95
NVDA| 44 192.2734 195.89
Other common aggregations are sum, avg, med, min, and dev (standard deviation):
q)select mid:med price, lo:min price, sd:dev price by sym from trade where sym in `AAPL`MSFT
sym | mid lo sd
----| ----------------------
AAPL| 271.02 255.6 8.092957
MSFT| 540.27 522.22 14.10905
Group by several columns by listing them:
q)select n:count i by sym, ex from trade where sym in `AAPL`MSFT, ex in `N`Q
sym ex| n
-------| -
AAPL N | 4
AAPL Q | 4
MSFT N | 4
MSFT Q | 1
A by column can also be an expression. Use xbar to group into time buckets, for example hourly open and close prices:
q)select open:first price, close:last price by 60 xbar time.minute from trade where sym=`AAPL
minute| open close
------| -------------
09:00 | 255.6 258.57
10:00 | 260.3 263.9
11:00 | 264.89 269.23
12:00 | 269.52 273.16
13:00 | 274.21 276.29
14:00 | 276.61 279.98
15:00 | 280.51 285.48
If a column isn't aggregated, the result holds a list of its values for each group:
q)select 3#price by sym from trade where sym in `AAPL`MSFT
sym | price
----| --------------------
AAPL| 255.6 256.24 256.57
MSFT| 522.27 522.22 522.22
With no select phrase at all, select by returns the last row of each group:
q)select by sym from trade where sym in `AAPL`MSFT
sym | time price size stop cond ex
----| ---------------------------------------------
AAPL| 0D15:59:36.297840840 285.48 54 0 F D
MSFT| 0D15:59:50.325049624 562.95 19 0 M C
Chain select statements¶
The result of select is a table, so you can query it with another select. Use this to filter on an aggregated value, for example to find the stocks with a volume-weighted average price above 500:
q)select from (select vwap:size wavg price by sym from trade) where vwap>500
sym | vwap
----| --------
META| 749.7631
MSFT| 539.7139
The inner query runs on trade, and the outer query runs on its result.
Limit the number of rows¶
To return the first n rows of the result, put n in square brackets after select. Use a negative number for the last rows, or a pair m n to return n rows starting at row m:
q)select[3] sym, time, price from trade where sym=`AAPL
sym time price
--------------------------------
AAPL 0D09:30:22.699448654 255.6
AAPL 0D09:32:29.061196651 256.24
AAPL 0D09:33:26.493750743 256.57
q)select[2 3] sym, time, price from trade where sym=`AAPL
sym time price
--------------------------------
AAPL 0D09:33:26.493750743 256.57
AAPL 0D09:41:01.716275195 258.16
AAPL 0D09:47:23.248078876 258.35
You can get the same rows with sublist, as in 3 sublist select sym, time, price from trade where sym=`AAPL, but n sublist select calculates the whole result before discarding most of it. select[n] applies the where clause, takes the first n rows, and only then calculates the select phrase.
Sort the result¶
To sort the result, put a column in square brackets after select, with > for descending or < for ascending order. Combine it with a limit to get, for example, the three highest prices:
q)select[3;>price] sym, time, price from trade
sym time price
--------------------------------
META 0D15:59:51.329812465 779.33
META 0D15:59:58.814063232 779.33
META 0D15:59:28.682203389 779.21
This is equivalent to sorting the result with xdesc or xasc:
q)3 sublist `price xdesc select sym, time, price from trade
sym time price
--------------------------------
META 0D15:59:51.329812465 779.33
META 0D15:59:58.814063232 779.33
META 0D15:59:28.682203389 779.21
To sort on several columns in different directions, chain xasc and xdesc, as in `sym xasc `price xdesc select …. See Reordering.
For storage-independent code, limit the query result
select[n] and select[>col] work on in-memory and memory-mapped splayed tables, but not on partitioned tables (see Limit and sort partitioned tables). Code that works before a table is partitioned can therefore fail after the storage layout changes.
In production code, use sublist with xasc or xdesc instead. They work on the result of a query on any table, whatever its storage:
q)3 sublist `price xdesc select sym, time, price from trade
Use variables in a query¶
A query can refer to variables as well as columns, for example a function parameter. q looks up each name in this order:
- A column of the table, or of a table it links to with a foreign key.
- A local variable or parameter of the function that contains the query.
- A global variable in the current namespace.
So if a variable has the same name as a column, the column wins. Here price>price compares the column with itself, so the global price is ignored and no rows match:
q)price:280
q)select from trade where sym=`AAPL, price>price
sym time price size stop cond ex
--------------------------------
To refer to the global anyway, name its namespace explicitly. `. `price is the global price in the root namespace:
q)count select from trade where sym=`AAPL, price>(`. `price)
9
It's clearer to give variables names that no column uses. In a function, this means naming the parameters differently from the columns:
q)f:{[minPrice] select sym, time, price from trade where sym=`AAPL, price>minPrice}
q)f 280
sym time price
--------------------------------
AAPL 0D15:15:49.822947644 280.51
AAPL 0D15:30:49.729203180 281.49
AAPL 0D15:33:16.008235746 282.17
...
Declare the parameters explicitly, even when you'd use the default names x, y, and z. q doesn't detect x, y, and z as implicit parameters when they appear only in the select, by, or where phrase, so the function takes one parameter and signals rank when you call it with two:
q){select from trade where sym=x, price>y}[`AAPL;280]
'rank
q)count {[s;p] select from trade where sym=s, price>p}[`AAPL;280]
9
A variable can supply a value, but not a column name. To choose columns at runtime, for example from a parameter, use functional qSQL instead.
Query a remote process¶
The examples so far run at the prompt of the process that holds the data. To run them from another process, make the process listen on a port, for example with \p 5100 in the session that holds the tables. Then open a connection from the client process and send the query as a string:
q)h:hopen 5100
q)h "select vwap:size wavg price by sym from trade where sym in `AAPL`MSFT"
sym | vwap
----| --------
AAPL| 270.8899
MSFT| 539.7139
A string is convenient for ad hoc queries, but if the query depends on values in the client, you have to convert them to text and join them into the string, for example "… sym in ",.Q.s1 `AAPL`MSFT. That is easy to get wrong for some types, and a server that evaluates arbitrary strings is open to injection.
Instead, send a function and its arguments as a list. The arguments travel as typed q values:
q)h ({[s] select vwap:size wavg price by sym from trade where sym in s}; `AAPL`MSFT)
sym | vwap
----| --------
AAPL| 270.8899
MSFT| 539.7139
In production, define the function on the server and send only its name and arguments. See Function call.
Performance¶
q has no query planner: it runs the query exactly as you write it. These guidelines help it do the least work.
Allocate secondary threads¶
Start the server with secondary threads, for example q -s 8. With secondary threads, multithreaded primitives such as sum and avg split their work across threads, and a by query on a column with the grouped or parted attribute processes its groups in parallel. Without them, every query runs on the main thread. Check the current number with \s.
Select only the columns you need¶
q calculates every column in the select phrase, so selecting fewer columns does less work. Because q doesn't rewrite your query, it can't drop columns that you select but never use. select from trade copies all seven columns of the matching rows, whereas select sym, price from trade copies two.
Put the most restrictive constraint first¶
Successive constraints run on fewer and fewer rows, so put the constraint that removes the most rows first. Two optimizations take precedence: the partition constraint must come first on a partitioned table, and a constraint that can use an attribute should be the first non-partition constraint. Order the remaining constraints by how many rows they remove.
Use attributes on the first constraint¶
An attribute tells q how a column is organized, so it can find matching rows without scanning the column. You can see them in the a column of meta trade: sym has the grouped (g) attribute, so q keeps a hash index of where each symbol occurs, and sym=`AAPL jumps straight to those rows. time has the sorted (s) attribute, so time within 0D10:00 0D10:10 finds the start and end of the range with a binary search.
q uses the attribute only when the column is in the first constraint, and unmodified on the left of the operator, as in where sym in `AAPL`MSFT, …. A constraint such as where price>100, sym=`AAPL can't use it, because sym is then evaluated only on the rows that passed price>100. To set an attribute on a column, see Set attribute.
Query on-disk tables¶
The queries on this page work unchanged on tables that are on disk, so you don't have to rewrite them when the data outgrows memory. The differences come from how the data is stored, and mostly affect the constraints at the start of the where clause and how you limit and sort.
Example database¶
To try the differences, generate eight trading days of the same kind of data into /tmp/testdb:
q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)buildPersistedDB["/tmp/testdb"; ([start: 2026.04.01; end: 2026.04.10])]
The generator skips weekends, so the database has one partition for each weekday between the two dates. Each partition holds the trade, quote, and nbbo tables. The root holds the splayed daily table of open, high, low, and close prices, and the reference tables master and exnames. Start q with the database loaded:
q /tmp/testdb
The examples use these tables:
trade: a partitioned table, with one partition for each date.dateis a virtual column, derived from the partition names.daily: a splayed table with the open, high, low, and close price of each stock on each day.
Constrain the partition column first¶
On a partitioned table, always constrain date first. q then reads only the partitions you need, instead of every partition:
q)select from trade where date=2026.04.10, sym=`AAPL
date sym time price size stop cond ex
-------------------------------------------------------------
2026.04.10 AAPL 0D09:30:00.024953777 307.17 78 0 N W
2026.04.10 AAPL 0D09:30:00.468672777 306.85 95 0 M I
2026.04.10 AAPL 0D09:30:01.400354761 306.47 18 0 V I
..
As a virtual column, date is available in the select phrase and in a by phrase, so you can group by it:
q)select vwap:size wavg price by date, sym from trade where date within 2026.04.09 2026.04.10, sym in `AAPL`MSFT
date sym | vwap
---------------| --------
2026.04.09 AAPL| 306.7272
2026.04.09 MSFT| 563.5195
2026.04.10 AAPL| 301.0052
2026.04.10 MSFT| 562.1728
A query without a constraint on date reads the sym column of every partition:
q)select n:count i by date from trade where sym=`AAPL
date | n
----------| ----
2026.04.01| 912
2026.04.02| 908
2026.04.03| 954
2026.04.06| 1027
2026.04.07| 987
2026.04.08| 992
2026.04.09| 974
2026.04.10| 1011
Only do that when you need the result for every date.
The row index is per partition¶
For a partitioned table, i is the index within each partition, not within the whole table:
q)select date, row:i, sym, price from trade where date in 2026.04.09 2026.04.10, i<2
date row sym price
--------------------------
2026.04.09 0 AAPL 305.36
2026.04.09 1 AAPL 305.43
2026.04.10 0 AAPL 307.17
2026.04.10 1 AAPL 306.85
Limit and sort partitioned tables¶
select[n] and select[>col] work on in-memory and memory-mapped splayed tables. They signal nyi on partitioned tables, where you must apply sublist, xasc, or xdesc to the query result instead:
q)-2 sublist select sym, time, price from trade where date=2026.04.10, sym=`AAPL
sym time price
--------------------------------
AAPL 0D15:59:50.087964470 302.12
AAPL 0D15:59:54.920091005 302.42
q)3 sublist `price xdesc select sym, time, price from trade where date=2026.04.10, sym=`AAPL
sym time price
--------------------------------
AAPL 0D09:33:35.719636885 309.06
AAPL 0D09:33:25.898759198 308.97
AAPL 0D09:33:15.956139712 308.87
On the splayed daily table, the limit and order expressions work directly:
q)select[3;>close] date, sym, close from daily
Aggregate across partitions¶
q runs a query on a partitioned table one partition at a time, then combines the results. It recognizes common aggregations, such as sum, avg, count, min, max, and wavg, and combines their partial results correctly:
q)select sum size by sym from trade where date in 2026.04.09 2026.04.10, sym in `AAPL`MSFT
sym | size
----| ------
AAPL| 108095
MSFT| 108837
If you wrap an aggregation in a lambda, q can't recognize it, so you get one result for each partition. Without a by phrase the query signals rank, and with one each group holds a list:
q)select {sum x} size from trade where date in 2026.04.09 2026.04.10, sym=`AAPL
'rank
q)select {sum x} size by sym from trade where date in 2026.04.09 2026.04.10, sym in `AAPL`MSFT
sym | size
----| -----------
AAPL| 53976 54119
MSFT| 55618 53219
To apply a custom aggregation, select the columns from disk first, then aggregate the in-memory result:
q)select {sum x} size by sym from select sym, size from trade where date in 2026.04.09 2026.04.10, sym in `AAPL`MSFT
sym | size
----| ------
AAPL| 108095
MSFT| 108837
This workaround materializes all matching rows in memory. Keep the partition and row filters as selective as possible; for large results, implement the custom aggregation per partition and combine its partial results instead.
Other qSQL statements¶
update, delete, and exec work differently on tables loaded from disk. For example, update on a table mapped from disk returns a modified copy in memory and leaves the files unchanged. On a partitioned table, update and delete signal par, and exec signals nyi. See Querying kdb+ data.
Query an on-disk table without loading the database
A table doesn't have to be loaded as part of a database to be queried. You can select from a single table on disk by its path. If the table has enumerated symbol columns, load the sym file first, or the symbol columns show their enumeration indices instead of the symbols:
q)sym:get `:/tmp/testdb/sym
q)select from `:/tmp/testdb/daily where date=2026.04.10, sym in `AAPL`MSFT
date sym open high low close price size
--------------------------------------------------------------
2026.04.10 AAPL 307.17 309.06 293.36 302.42 1.62901e+07 54119
2026.04.10 MSFT 562.01 572.51 551.67 562.52 2.991827e+07 53219
Performance on disk¶
The performance guidelines for in-memory tables apply on disk too. These points are specific to on-disk data:
- Constrain
datefirst. The partition column is the most restrictive constraint, because it decides which partitions q opens. - Group by
datefirst. When you group by the partition column and other columns, put it first in thebyphrase, as inby date, sym. - Select only the columns you need. On-disk tables store each column in its own file, and q maps only the columns a query refers to.
select from trade where date=2026.04.10maps every column of the partition, whereasselect sym, price from trade where date=2026.04.10maps two. - Use secondary threads. With secondary threads, q maps the columns of each partition in parallel, and a
byquery on a partitioned table processes the partitions in parallel, for exampleq /tmp/testdb -s 8. - Use the parted attribute on
sym. In the example database,symin each partition oftradehas the parted (p) attribute: all rows for a stock are stored together, sosym=`AAPLjumps straight to them. The attribute is used only whensymis in the first constraint after the partition column, and unmodified on the left of the operator, as inwhere date=2026.04.10, sym in `AAPL`MSFT. A constraint such aswhere date=2026.04.10, price>100, sym=`AAPLcan't use it. See Partitioned tables.
Next steps¶
- Exec, update, and delete statements: return lists and dictionaries, change columns, and remove rows or columns.
- Joins: combine
tradewith other tables, for examplemaster. - Functional qSQL: build queries whose columns or constraints are decided at runtime.