# 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](../../ref/select.md).

You can also process a table with basic q constructs, as described in [Tables](../basics/data_structures/tables.md). 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
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
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
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](#query-on-disk-tables) for the few differences.

## Example data

The examples query US equity trades and quotes generated by the [datagen module](../../modules/datagen/overview.md). To create the tables in memory:
```q
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
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
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
..
```

To select all columns except a few, don't list the ones you want. Use [`delete`](exec-update-delete.md#delete-columns) to drop the ones you don't, for example `delete stop, cond from trade`. `delete` returns a modified copy and leaves the original table unchanged.

## Virtual column `i`

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
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
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`](../../ref/distinct.md) keyword inside an aggregation:
```q
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
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
..
```

An expression can be as complex as you need, because you can use the whole q language inside a `select`. This query returns the day's high and low prices, and the time each was reached:
```q
q)select high:max price, low:min price, time_high:(first time where price=max price), time_low:(first time where price=min price) from trade where sym=`ZAP
high low time_high            time_low
--------------------------------------------------
30.7 29  0D17:56:14.661171343 0D07:00:00.059735938
```

Each part works on whole columns:

- `max price` and `min price` aggregate the column to a single value.
- `price=max price` compares every price with the maximum and returns a boolean vector, with `1b` where the price equals the high.
- [`where`](../../ref/where.md) turns that boolean vector into the positions of the `1b`s, and `time where ...` picks the times at those positions.
- [`first`](../../ref/first.md) keeps the earliest of them, in case the high or low occurred more than once.

Expressions see the whole column, and you can also refer to other rows. Here, [`prev`](../../ref/next.md#prev) shifts the column by one row to calculate the price change between consecutive trades:
```q
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
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
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
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
q)select time, notional from (select time, notional:price*size from trade) where notional>20000
```

### Put the join operator in parentheses

> **Warning — 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](../../ref/join.md) 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`](../../ref/sv.md). Without parentheses, q reads ``string sym,'ex`` as two columns, `string sym` and `'ex`:
```q
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
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](../../ref/cond.md) (`$[…]`) 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](../../ref/vector-conditional.md) `?[…]` instead, which chooses item by item:
```q
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
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`](../../ref/vs.md), and returns the first part. Applied to the whole column, `vs` receives a list of strings instead of a single string, and fails:
```q
q)select id, first "," vs tags from t
'type
```

Use [`each`](../../ref/each.md) to apply the function to one string at a time:
```q
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
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`](../../ref/within.md):
```q
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
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`](../../ref/lesser.md). ``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
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](#limit-the-number-of-rows) instead.

### Filter strings with the `like` keyword

Use [`like`](../../ref/like.md) 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
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`](../../ref/lower.md) and use a lowercase pattern:
```q
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`](../../ref/fby.md). `(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
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
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
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
```

#### Elegance versus performance

`fby` is elegant: the query reads like the question it answers, and you can see at a glance what it does. Readability is often the most important property of a query, and `fby` is a good default. When a query runs often or on large tables, though, an equivalent query without `fby` can be considerably faster. `fby` groups the rows, applies the function to one group after another, and then spreads the results back over every row. The function runs on the main thread, so `fby` gains little from [secondary threads](#allocate-secondary-threads).

For example, to select the last three trades before 15:00 for each stock:
```q
q)select from trade where time<15:00:00, 3>(idesc;i) fby sym
sym    time                 price  size stop cond ex
----------------------------------------------------
BIIB   0D13:16:24.973217980 163.5  15   0    7    M
ORCL   0D13:47:04.247128330 299.49 94   0    H    S
ISHG   0D14:06:22.805570013 78.49  50   0    4    T
AMTM   0D14:06:37.297349663 26.75  89   0    H    I
CAMP   0D14:08:03.954523971 3.01   54   1    8    J
..
```

`(idesc;i) fby sym` applies [`idesc`](../../ref/desc.md#idesc) to the row indices of each stock. `idesc` returns the positions that would sort its argument in descending order. The row indices of a group are always in ascending order, so for a group of `n` rows, `idesc` returns `n-1`, `n-2`, …, `0`: the last row of each stock gets `0`, and `3>` keeps the last three rows. The result keeps the order of the table, so the stocks are interleaved in time order.

`idesc` sorts each group, but the order of the row indices is already known, so the sort does no useful work. Number the rows of each group from the end directly instead:
```q
q)r1: select from trade where time<15:00:00, 3>(idesc;i) fby sym
q)r2: select from trade where time<15:00:00, 3>({reverse til count x};i) fby sym
q)r1 ~ r2
1b
```

The function uses only the number of rows in each group, so the column you pass doesn't matter. On a `trade` table of 100 million rows, generated by the datagen module with `tradesPerDay: 40000000`, the query without `idesc` takes about 35% less time.

A query that doesn't use `fby` at all can be faster still. Group the columns by stock, take the last three items of each list, and use [`ungroup`](../../ref/ungroup.md) to turn the lists back into rows:
```q
q)ungroup select sym, -3 sublist' time, -3 sublist' price, -3 sublist' size, -3 sublist' stop, -3 sublist' cond, -3 sublist' ex from select time, price, size, stop, cond, ex by sym from trade where time<15:00:00
sym    time                 price  size stop cond ex
----------------------------------------------------
AAPL   0D14:51:14.098196813 279.17 57   0    B    X
AAPL   0D14:55:11.312317265 279.49 38   0    F    Q
AAPL   0D14:59:00.371547822 279.98 29   0    4    I
AGEN   0D14:46:53.997279062 4.3    45   0    Q    A
AGEN   0D14:50:48.444788627 4.31   60   0    5    X
AGEN   0D14:57:25.832790802 4.32   45   0    H    P
..
```

The result has the same rows as before, but it is ordered by stock.

The `by` query uses secondary threads, and the per-group work is a cheap `sublist` instead of a function call that `fby` spreads back over every row. On the same table of 100 million trades with 32 secondary threads, this query is about 18 times faster than the `fby` query without `idesc`, and about 28 times faster than the query with `idesc`. Without secondary threads, it is about 1.5 and 2.3 times faster. It isn't cheaper in memory, though: it copies every column of the matching rows into the grouped lists, so its peak memory is about the same as that of the `fby` queries.

Another way to remove `fby` is to split the query into one query for each stock. Every row of a per-stock query belongs to the same group, so `fby` has nothing left to do, and you apply the function to the column directly. For example, this query numbers the runs of unchanged prices of each stock with `fby`, and groups by them:
```q
q)syms: `MSFT`AAPL`NVDA
q)f1: select first price, cnt: count size, sum size by sym, pricegroup: (sums differ@; price) fby sym from trade where sym in syms
```

The per-stock version runs the queries in parallel with [`peach`](../../ref/each.md), and a helper function stitches the results back together. Each per-stock query returns its own table, without a `sym` column, so `stitch` repeats each symbol once for each row of its result, and joins the symbols to the rows:
```q
q)stitch: {[s; r] ([] sym: s where count each r) ,' raze r}
q)f2: 2!stitch[s; {0!select first price, cnt: count size, sum size by pricegroup: sums differ price from trade where sym=x} peach s: asc distinct syms]
q)f1 ~ f2
1b
q)3#f2
sym  pricegroup| price  cnt size
---------------| ---------------
AAPL 1         | 255.6  1   44
AAPL 2         | 256.24 1   90
AAPL 3         | 256.57 1   37
```

The same rewrite removes `fby` from a `where` clause, such as `` price=(max;price) fby sym ``: in a per-stock query, write `price=max price`. [Optimize by hand](#optimize-by-hand) explains why the per-stock queries are faster, and the details that make `f2` match `f1` exactly.

### Filter using another table

A constraint can use the result of another query. Use [`exec`](../../ref/exec.md) to return a list of values from the other table, and [`in`](../../ref/in.md) to keep the rows that match. For example, to calculate the volume-weighted average price of the companies whose description ends in `Inc.`:
```q
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 …`.

> **Tip — Foreign keys**
>
>
> If a column is a [foreign key](../interact_with_databases/relationships/foreign-keys.md) 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](../interact_with_databases/relationships/foreign-keys.md#query-across-the-key).
>
### Drop rows with nulls

Use [`null`](../../ref/null.md) 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
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
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
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`](../../ref/sum.md), [`avg`](../../ref/avg.md), [`med`](../../ref/med.md), [`min`](../../ref/min.md), and [`dev`](../../ref/dev.md) (standard deviation):
```q
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
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`](../../ref/xbar.md) to group into time buckets, for example hourly open and close prices:
```q
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
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
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
```

If you need the first row of each group instead, reverse the table with [`reverse`](../../ref/reverse.md):
```q
q)select by sym from reverse[trade] where sym in `AAPL`MSFT
sym | time                 price  size stop cond ex
----| ---------------------------------------------
AAPL| 0D09:30:22.699448654 255.6  44   0    P    S
MSFT| 0D09:33:30.593281541 522.27 57   0    6    P
```

This is short, but expensive on a large table. `reverse` copies every column of the whole table before the `where` clause runs, and the copy has no attributes, so `` sym in `AAPL`MSFT `` can't use the grouped attribute and scans every row. On a `trade` table of 100 million rows, the query takes about 2 seconds and 6.8 GB of memory, against about a millisecond for the query without `reverse`. Filter first and reverse only the matching rows, or use [`first`](../../ref/first.md) on each column you need. Both return the same result:
```q
q)select by sym from reverse select from trade where sym in `AAPL`MSFT
q)select first time, first price, first size, first stop, first cond, first ex by sym from trade where sym in `AAPL`MSFT
```

On the same table, with 32 secondary threads, the first query takes about 0.4 seconds and 350 MB, because it copies only the AAPL and MSFT rows. The second takes about 50 milliseconds and almost no memory, because it doesn't copy the matching rows at all.

## 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
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
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`](../../ref/sublist.md), 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
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`](../../ref/desc.md#xdesc) or [`xasc`](../../ref/asc.md#xasc):
```q
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](../basics/data_structures/tables.md#reordering).

??? tip "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](#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`](../../ref/sublist.md) with [`xasc`](../../ref/asc.md#xasc) or [`xdesc`](../../ref/desc.md#xdesc) instead. They work on the result of a query on any table, whatever its storage:
    ```q
    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:

1. A column of the table, or of a table it links to with a foreign key.
2. A local variable or parameter of the function that contains the query.
3. 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
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
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
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
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, there is no perfect solution, and each option has a trade-off:

- [Functional qSQL](../../ref/funsql.md) takes the column names as symbols, so the query is built from q values instead of text. It is safe to build from untrusted input, but harder to read and write than a `select` statement.
- [A query built as a string](#build-a-query-as-a-string) keeps the readable `select` syntax, but you are responsible for converting every value to text correctly, and the server evaluates text.
- The [Printf module](../../modules/printf/overview.md) takes some of the string handling off your hands, but adds a dependency.

### Build a query as a string

Join the pieces of the query into a string and evaluate it with [`value`](../../ref/value.md). For example, to select the columns named in a variable:
```q
q)fields: `sym`price
q)3#value "select ", (",", sv string fields), " from trade where sym=`AAPL"
sym  price
-----------
AAPL 255.6
AAPL 256.24
AAPL 256.57
```

The query is then only a string until q evaluates it, so q can't check it in advance, and a mistake shows up at runtime. If the query depends on values, 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: ``.Q.s1 `$"a b"`` returns ``"`a b"``, which q reads as the symbol `` `a `` followed by the name `b`. A process that evaluates strings built from untrusted input is also open to injection, because the input can contain q code.

The Printf module lets you write the query as a template, and `%r` converts each argument to its q text form. It doesn't remove the need to validate the input, and your code now depends on the module:
```q
q)([printf]): use `kx.printf
q)3#value printf ("select %s from trade where sym=%r"; ",", sv string fields; `AAPL)
sym  price
-----------
AAPL 255.6
AAPL 256.24
AAPL 256.57
```

## 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](../io_and_communication/basics.md) from the client process and send the query as a string:
```q
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 it has [drawbacks](#build-a-query-as-a-string) when the query depends on values in the client: you have to convert them to text correctly, 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
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](overview.md#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](../../ref/cmdline.md#-s-secondary-threads), for example `q -s 8`. With secondary threads, [multithreaded primitives](../performance/code-optimization.md#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](../../ref/set-attribute.md) 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](../../ref/set-attribute.md).

### Optimize by hand

The guidelines so far keep a query readable. The technique in this section rewrites a query by hand into a form that q can run faster, but that is longer and harder to read. Use it only where performance matters more than readability, for example in a query that runs often on a busy server, and measure the gain on your own data first.

A query that uses `fby` can often be rewritten into a faster equivalent too. See [Elegance versus performance](#elegance-versus-performance).

The technique splits a query on several symbols into one query for each symbol, and runs those queries in parallel with [`peach`](../../ref/each.md). They need secondary threads to gain anything from the parallelism. Each per-symbol query returns its own table, without a `sym` column, so a helper function stitches the results back together. It repeats each symbol once for each row of its result, and joins the symbols to the rows:
```q
q)stitch: {[s; r] ([] sym: s where count each r) ,' raze r}
```

#### Replace `in` with parallel `=` queries

This query calculates five-minute open, high, low, and close prices for three stocks:
```q
q)syms: `MSFT`AAPL`NVDA
q)r1: select o: first price, h: max price, l: min price, c: last price, s: sum size by sym, 5 xbar time.minute from trade where sym in syms
```

The manual version runs a query for each symbol, with `` sym=x `` instead of `sym in syms`, and groups by time only:
```q
q)r2: 2!stitch[s; {0!select o: first price, h: max price, l: min price, c: last price, s: sum size by 5 xbar time.minute from trade where sym=x} peach s: asc distinct syms]
q)r1 ~ r2
1b
q)3#r2
sym  minute| o      h      l      c      s
-----------| -------------------------------
AAPL 09:30 | 255.6  256.57 255.6  256.57 171
AAPL 09:40 | 258.16 258.16 258.16 258.16 55
AAPL 09:45 | 258.35 258.57 258.35 258.57 174
```

The rewrite does less work in three ways: `` sym=x `` uses the grouped attribute to go straight to the rows of one symbol, each query groups its rows by time alone, and `peach` runs the queries for different symbols at the same time.

To return exactly what the original returns, the rewrite needs three details:

- `asc distinct syms` puts the symbols in the order that `by sym` would, and drops duplicates, which would otherwise repeat their rows.
- `0!` unkeys each per-symbol result, so that `stitch` can join the columns.
- `2!` keys the stitched table on `sym` and `minute`, the two columns of the original `by` phrase.

## 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
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:
```bash
q /tmp/testdb
```

The examples use these tables:

- `trade`: a partitioned table, with one partition for each date. `date` is 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
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
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
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
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
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
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
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
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
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.

### Look back across partition boundaries

Some questions don't fit inside one partition. To get the last trade price of every instrument, the obvious (and fast) query reads only the latest partition:
```q
q)select last price by sym from trade where date=max date
```

The result holds only the instruments that traded on that date. Illiquid stocks, warrants, rights, and halted stocks can go days without a trade, so they're missing. To fill the gaps, you have to look back into earlier partitions, but no fixed window is guaranteed to be long enough. A constraint such as `date within (d-7;d)` reads seven partitions for every instrument, including the ones the latest partition already covers, and can still miss an instrument that last traded eight days ago.

Instead, take the universe of instruments from the reference table, and walk back one partition at a time, querying only the instruments still missing. Stop when every instrument has a price, or after `n` partitions, so that an instrument that never trades doesn't make q read the whole database:
```q
lastPrice:{[d;n]
  todo:(select sym from master where date=d)`sym;   / get the universe of instruments
  ds:n sublist desc date where date<=d;
  r:();
  while[count[todo]&count ds;
    p:0!select last date, last time, last price by sym from trade where date=first ds, sym in todo;
    r,:p;
    todo:todo except p`sym;
    ds:1_ds];
  `sym xkey r}
```
```q
q)select from lastPrice[2026.04.01; 10] where sym in `T`T.PRA`TACH.U
sym   | date       time                 price
------| -------------------------------------
T     | 2026.04.01 0D19:59:30.820971201 28.35
T.PRA | 2026.04.01 0D19:00:00.003109753 19.78
TACH.U| 2026.03.31 0D15:47:12.318604127 10.7
```

`TACH.U`, a unit of a special-purpose acquisition company, has no trades on 2026.04.01, so the function takes its last price from the 2026.03.31 partition.

The `date` column of the result shows how far back q had to go for each instrument. Instruments that don't appear didn't trade in the last `n` partitions. Each iteration reads one partition, and because `` sym in todo `` is the first constraint after the partition column, q uses the parted attribute to read only the rows of the missing instruments.

??? note "Larger partitions don't remove the problem"

    The longer the period a partition covers, the more instruments trade in it, so with monthly partitions the latest partition misses fewer instruments. But each partition holds longer vectors. A query about a single day then has to scan, and hold in memory, the columns for the whole month, which slows down every query that works within a partition.

??? note "Use derived tables to avoid scanning old partitions"

    A typical workaround for look-back problems is to maintain smaller derived tables alongside the raw data. An open, high, low, and close (OHLC) table, such as `daily` in the [datagen](https://github.com/KxSystems/datagen/blob/main/docs/references/capmkts.md) example database, holds one row for each instrument and day, instead of millions of trades. A look-back query on it reads a few values for each day, without scanning large `trade` partitions from the past. You can go one step further: at the end of each day, write the latest value for every instrument in the universe, and carry forward the previous day's value for instruments that had no update. The latest value of every instrument is then a query on a single partition. This works naturally for quotes, because a bid and an ask are a state: they stand until the next quote replaces them. A trade is an event, so a carried-forward trade price doesn't mean the instrument traded that day. If you carry trades forward, keep the date and time of the original trade with the price, so that queries can tell a stale price from a fresh one.

    A derived table adds a dependency. It's only correct while it matches the data it's derived from, so you must guarantee that it doesn't get out of sync. Build it in the same end-of-day process that writes the raw partition, and rebuild it whenever you correct, reload, or backfill that partition. Otherwise queries on the derived table silently return stale results.

### 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](overview.md#qsql).

??? note "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
    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 `date` first.** The partition column is the most restrictive constraint, because it decides which partitions q opens.
- **Group by `date` first.** When you group by the partition column and other columns, put it first in the `by` phrase, as in `by 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.10` maps every column of the partition, whereas `select sym, price from trade where date=2026.04.10` maps two.
- **Use secondary threads.** With [secondary threads](../../ref/cmdline.md#-s-secondary-threads), q maps the columns of each partition in parallel, and a `by` query on a partitioned table processes the partitions in parallel, for example `q /tmp/testdb -s 8`.
- **Use the parted attribute on `sym`.** In the example database, `sym` in each partition of `trade` has the parted (`p`) attribute: all rows for a stock are stored together, so `` sym=`AAPL `` jumps straight to them. The attribute is used only when `sym` is in the first constraint after the partition column, and unmodified on the left of the operator, as in `` where date=2026.04.10, sym in `AAPL`MSFT ``. A constraint such as `` where date=2026.04.10, price>100, sym=`AAPL `` can't use it. See [Partitioned tables](../interact_with_databases/partition.md).

## Next steps

- [Exec, update, and delete statements](exec-update-delete.md): return lists and dictionaries, change columns, and remove rows or columns.
- [Joins](../../ref/joins.md): combine `trade` with other tables, for example `master`.
- [Functional qSQL](../../ref/funsql.md): build queries whose columns or constraints are decided at runtime.
