SELECT

Choose the columns and values that a query gives back, and the table they come from.

• Category: Querying
• Keywords: query, read rows, columns, *, table.*, AS, alias, column name, FROM, DISTINCT, unique rows, SELECT without FROM, calculator, missing comma, select, from, distinct
• See also: WHERE, ORDER BY, LIMIT, OFFSET, GROUP BY and HAVING, Joins, UNION and subqueries, Views

SELECT reads data. You say which values you want (the select list), and from which table (FROM). Every other part (WHERE, GROUP BY, ORDER BY, ...) is optional and has its own help page.

A select item can be a column, a calculation, a function or a constant. Give it a name with AS.

Example: Today's change of every symbol, like a market summary (sample data: sample)

SELECT symbol, open, close,
       round(close - open, 2) AS change,
       round((close - open) / open * 100, 2) AS change_pct
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY change_pct DESC;

Result:

(3 rows)

SELECT

Syntax:

SELECT [DISTINCT] item [[AS] alias], ...
[FROM source [JOIN ...]]
[WHERE condition]
[LATEST ON time PARTITION BY column, ...]
[SAMPLE BY interval ...]
[GROUP BY expression, ...]
[HAVING condition]
[UNION ALL SELECT ...]
[ORDER BY expression [ASC | DESC], ...]
[LIMIT n [OFFSET m]]

The parts must be written in this order. KDB does the work in another order:

• Returns: a table of rows. The column names come from the aliases, else from the items.
• Notes: names of tables, columns and functions do not care about case (CLOSE is close). Text values do: 'CITY' is not 'city'.

Example: Trade log: the value of each trade on 2026-09-09 (sample data: sample)

SELECT transaction_id, symbol, quantity, rate, quantity * rate AS value
FROM trade_log
WHERE date IN '2026-09-09'
ORDER BY value DESC
LIMIT 4;

Result:

(4 rows)

Example: App users: name, email and plan (sample data: query-app)

SELECT username, lower(email) AS email, upper(plan) AS plan
FROM app_users
WHERE id <= 4;

Result:

(4 rows)

Example: Accounting: the amount of each journal line, debit or credit (sample data: query-app)

SELECT line_id, entry_id, account, coalesce(debit, 0) - coalesce(credit, 0) AS amount, memo
FROM ledger
WHERE entry_id IN (2, 8);

Result:

(5 rows)

, table.

Syntax:

SELECT * FROM table
SELECT alias.*, other_item, ... FROM table alias JOIN ...

* means every column of every table in FROM, in table order. alias.* means every column of one table.

• Returns: the stored columns and the computed columns (AS (...)) of the table.
• Notes: * cannot be used together with aggregates or SAMPLE BY. In programs, list the columns you need: KDB then takes only those columns out of each stored row, which is faster on wide tables (EXPLAIN shows build 3 of 7 stored column(s) per row), and a new column in the table does not change your result.

Example (sample data: sample)

SELECT * FROM brokers;

Result:

(5 rows)

Example: All columns of the trade, plus the buyer's name (sample data: sample)

SELECT f.*, b.name AS buyer_name
FROM trade_log f
JOIN brokers b ON b.id = f.buyer
WHERE f.transaction_id = 100017;

Result:

(1 row)

Example: * and count(*) do not mix (sample data: sample; shows an error)

SELECT *, count(*) FROM brokers;

Result:

Error: line 1, column 8: * cannot be combined with aggregates or SAMPLE BY

AS: column names

Syntax:

item AS alias
item alias
item AS "any name"

AS gives an output column a name. The word AS is optional.

• Notes: a missing comma is not an error: SELECT symbol close gives one column named close that holds the symbol. Check your commas.
• A reserved word (like order, select, from) needs double quotes: AS "order".
• The words ASOF, CROSS, FULL, HAVING, INNER, LATEST, LEFT, LT, OVER, REFRESH, RIGHT, SAMPLE, SPLICE, UNION and USING need AS to be an alias: close AS left works, close left does not.
• An alias can be used in ORDER BY, but not in WHERE, GROUP BY or HAVING (write the expression again there). See ORDER BY and GROUP BY.

Example (sample data: sample)

SELECT symbol, close AS last_price, close last, close * 2, round(close) AS "Close (whole dollars)"
FROM ochl_day
WHERE time IN '2026-09-10';

Result:

(3 rows)

Example: Aggregates without an alias get their text as the name (sample data: sample)

SELECT symbol, count(*), max(high) AS highest
FROM ochl_day
GROUP BY symbol;

Result:

(3 rows)

Example: A missing comma quietly renames the column (sample data: sample)

SELECT symbol close FROM securities LIMIT 2;

Result:

(2 rows)

Example: A reserved word as a name needs double quotes (sample data: sample; shows an error)

SELECT symbol AS order FROM securities;

Result:

Error: line 1, column 18: expected an alias after AS but found the reserved word 'order' (write it in double quotes, "order", to use it as a name)

Example: The fix (sample data: sample)

SELECT symbol AS "order" FROM securities LIMIT 2;

Result:

(2 rows)

SELECT without FROM

Syntax:

SELECT expression [[AS] alias], ...

Without FROM, KDB computes the values once and gives one row. Use it like a calculator, or to test a function.

• Returns: one row.
• Notes: WHERE still works (a false condition gives 0 rows). * needs a FROM table.

Example (sample data: sample)

SELECT 1 + 1 AS two, round(100000 * 1.08, 2) AS with_interest, upper('city') AS symbol;

Result:

(1 row)

Example: What is 11:00 Nepal time in UTC? (sample data: sample)

SELECT CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) AS market_open_utc;

Result:

(1 row)

Example: * needs a table (sample data: sample; shows an error)

SELECT *;

Result:

Error: line 1, column 8: * needs a FROM table

FROM

Syntax:

FROM table [[AS] alias]
FROM database.table [[AS] alias]
FROM view [[AS] alias]
FROM (SELECT ...) [AS] alias

FROM names the table the rows come from. It can also be a view or a subquery in ( ).

• Notes: without ORDER BY, a time-series table gives rows partition by partition, sorted by its SORT BY columns (usually symbol, then time). A relational table gives rows in key order. Do not depend on this: add ORDER BY when the order matters.
• Names with capital letters or spaces can be written in double quotes: FROM "Ochl_Day" is the same table as FROM ochl_day.

Example: A table with a short name (sample data: sample)

SELECT s.symbol, s.sector FROM securities AS s WHERE s.sector = 'Banking';

Result:

(2 rows)

Example: Rows of a subquery: trades per symbol, only the busy ones (sample data: sample)

SELECT t.symbol, t.trades
FROM (SELECT symbol, count(*) AS trades FROM trade_log GROUP BY symbol) t
WHERE t.trades > 5;

Result:

(3 rows)

Example: A wrong table name (sample data: sample; shows an error)

SELECT * FROM ochl_days;

Result:

Error: line 1, column 15: table or view 'ochl_days' does not exist

DISTINCT

Syntax:

SELECT DISTINCT item, ... FROM ...

DISTINCT removes repeated rows from the result. Two rows are the same when all their output columns are equal.

• Returns: each different row once.
• NULL: NULL values count as equal, so many NULL rows become one.
• Notes: to count different values, use count(DISTINCT x) (see aggregate functions). DISTINCT works on the output columns, after GROUP BY.
• SELECT DISTINCT day on a table with one partition per day is answered from the list of partitions, without reading rows (see answers without reading rows).

Example (sample data: sample)

SELECT DISTINCT sector FROM securities ORDER BY sector;

Result:

(4 rows)

Example: Which broker bought which symbol on 2026-09-09 (sample data: sample)

SELECT DISTINCT buyer, symbol
FROM trade_log
WHERE date IN '2026-09-09'
ORDER BY buyer, symbol;

Result:

(7 rows)

Example: Cities of app users (NULL appears once) (sample data: query-app)

SELECT DISTINCT city FROM app_users ORDER BY city;

Result:

(5 rows)

Example: Count different values instead (sample data: sample)

SELECT count(DISTINCT symbol) AS symbols_traded, count(*) AS trades FROM trade_log;

Result:

(1 row)