`select`

Select all or part of a table, possibly with new columns

select is a qSQL query template and varies from regular q syntax.

For the Select operator ?, see Functional SQL

Syntax

Below, square brackets mark optional elements.

select [L_exp] [p_s] [by p_b] from t_exp [where p_w]

qSQL syntax

The select query returns a table for both call-by-name and call-by-value.

Minimal form

The minimal form of the query returns the evaluated table expression.

q)tbl:([] id:1 1 2 2 2;val:100 200 300 400 500)
q)select from tbl
id val
------
1  100
1  200
2  300
2  400
2  500

Select phrase

The Select phrase specifies the columns of the result table, one per subphrase.

Absent a Select phrase, all the columns of the table expression are returned. (Unlike SQL, no * wildcard is required.)

q)t:([] c1:`a`b`c; c2:10 20 30; c3:1.1 2.2 3.3)

q)select c3, c1 from t
c3  c1
------
1.1 a
2.2 b
3.3 c

q)select from t
c1 c2 c3
---------
a  10 1.1
b  20 2.2
c  30 3.3

A computed column in the Select phrase cannot be referred to in another subphrase.

Limit expression

To limit the returned results you can include a limit expression L_exp

select[n]
select[m n]
select[order]
select[n;order]
select distinct

where

n limits the result to the first n rows of the selection if positive, or the last n rows if negative
m is the number of the first row to be returned: useful for stepping through query results one block of n at a time
order is a column (or table) and sort order: use < for ascending, > for descending

select[3;>price] from bids where sym=s,size>0

This would return the three best prices for symbol s with a size greater than 0.

This construct works on in-memory tables but not on memory-mapped tables loaded from splayed or partitioned files.

Performance

select[n] applies the Where phrase on all rows of the table, and takes the first n rows, before applying the Select phrase.

So if you are paging it is better to store the result of the query somewhere and select[n,m] from there, rather than run the filter again.

select distinct returns only unique records in the result.

By phrase

A select query that includes a By phrase returns a keyed table. The key columns are those in the By phrase; values from other columns are grouped, i.e. nested.

q)k:`a`b`a`b`c
q)v:10 20 30 40 50

q)select c2 by c1 from ([]c1:k;c2:v)
c1| c2
--| -----
a | 10 30
b | 20 40
c | ,50

q)v group k   / compare the group keyword
a| 10 30
b| 20 40
c| ,50

Unlike in SQL, columns in the By phrase

are included in the result and need not be specified in the Select phrase
can include computed columns

The SQL GROUP BY statement

The ungroup keyword reverses the grouping, though the original order is lost.

q)ungroup select c2 by c1 from ([]c1:k;c2:v)
c1 c2
-----
a  10
a  30
b  20
b  40
c  50

q)t:([] name:`tom`dick`harry`jack`jill;sex:`m`m`m`m`f;eye:`blue`green`blue`blue`gray)
q)t
name  sex eye
---------------
tom   m   blue
dick  m   green
harry m   blue
jack  m   blue
jill  f   gray

q)select name,eye by sex from t
sex| name                 eye
---| ------------------------------------------
f  | ,`jill               ,`gray
m  | `tom`dick`harry`jack `blue`green`blue`blue

q)select name by sex,eye from t
sex eye  | name
---------| ---------------
f   gray | ,`jill
m   blue | `tom`harry`jack
m   green| ,`dick

A By phrase with no Select phrase returns the last row in each group.

q)select by sex from t
sex| name eye
---| ---------
f  | jill gray
m  | jack blue

Where there is a By phrase, and no sort order is specified, the result is sorted ascending by its key.

Cond

Cond is not supported inside query templates: see qSQL.

delete, exec, update
qSQL, Functional SQL
Q for Mortals §9.3 The select Template