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 [Lexp] [ps] [by pb] from texp [where pw]
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 Lexp
select[n]
select[m n]
select[order]
select[n;order]
select distinct
where
n
limits the result to the firstn
rows of the selection if positive, or the lastn
rows if negativem
is the number of the first row to be returned: useful for stepping through query results one block ofn
at a timeorder
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 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