SELECT statement

Selects data from a FeatureBase table.

BNF diagrams

expr

top_clause

expr

select_list

expr expr

from_clause

expr expr expr

where_clause

expr

group_by_clause

expr

having_clause

expr

order_by_clause

expr expr

Syntax

SELECT
  [DISTINCT]
  [<top_clause>]
  {<select_list>}
  {<from_clause>}
  {<where_clause>}
  [<group_by_clause>]
  [<having_clause>]
  [<order_by_clause>];

Arguments

Argument	Description	Required	Further information
`DISTINCT`	Keyword that specifies only unique rows exist in the output	Optional
top_clause	Specify a limit to apply to the number of rows returned in the output.	No	The `expr` used in the TOP clause must be an integer literal.
select_list	A series of expressions separated by commas contains the items selected to form the output result set.	Yes	select_list
from_clause	A list of table_or_subquery expressions that specify which relations to select data from.	Yes	from_clause
where_clause	An expression that defines a filter condition for the rows returned by the query.	Yes	Can be any constant, function or combination joined by operators or a subquery.
group_by_clause	Separates the results into groups of rows allowing aggregates to be performed on each group.	Optional	group_by_clause
having_clause	Pass aggregates to filter on based on conditions.	Optional
order_by_clause	Comma-separated column name, column alias or column position in the SELECT list used to specify the order data is returned.	Optional	Results can be ordered `ASC`ending or `DESC`ending.

Additional information

select_list information

expr expr

* wildcard represents all columns
<qualifier>.* limits the results to all columns based on the specified qualifier
expr can be any constant, function or combination thereof joined by operators, or a subquery
Items in the select_list can be aliased with a column_alias
Any column referenced in a non-aggregated expression in the select_list must also appear in the group_by list

from_clause information

expr expr

The table_or_subquery expression can be:

a table_name or table_valued_function
a parenthesized SELECT statement Both expressions can be aliased with a table_alias

group_by_clause information

expr

column_expr specifies a column or non-aggregate calculation on a column which:

must exist in the from_clause
is not required to appear in the select_list

A column must appear in the group_by_clause if it is referenced in a non-aggregated expression in the select_list

Wildcards

Wildcards are used with the LIKE clause.

SQL statement wildcards

Examples

SELECT statement with wildcard

SELECT * FROM services WHERE servicelist LIKE '%free%';

SELECT COUNT

SELECT COUNT(*) FROM github-stats;

SELECT TOP

SELECT TOP(10) * FROM github-stats;

Point SELECT

SELECT * FROM tbl WHERE _id = 1
SELECT fld1, fld2 FROM tbl WHERE _id = 1
SELECT _id, fld FROM tbl WHERE _id = 1

SELECT DISTINCT

SELECT DISTINCT fld FROM tbl

SELECT COUNT

SELECT count(*) FROM tbl
SELECT count(*) FROM tbl WHERE fld = 1
SELECT count(*) FROM tbl WHERE fld1 = 1 AND fld2 = 2
SELECT count(distinct fld) FROM tbl

SELECT ids FROM segment

SELECT _id FROM tbl WHERE fld = 1
SELECT _id FROM tbl WHERE fld in (1, 2)
SELECT _id FROM tbl WHERE fld1 = 1 LIMIT 1
SELECT _id FROM tbl WHERE fld1 = 1 AND fld2 = 2

SELECT int function

SELECT min(fld) FROM tbl
SELECT max(fld) FROM tbl
SELECT sum(fld) FROM tbl
SELECT avg(fld) FROM tbl
SELECT min(fld) FROM tbl WHERE fld = 1
SELECT max(fld) FROM tbl WHERE fld = 1
SELECT sum(fld) FROM tbl WHERE fld = 1
SELECT avg(fld) FROM tbl WHERE fld = 1

GROUP BY

SELECT fld, count(*) FROM tbl GROUP BY fld
SELECT fld1, fld2, count(*) FROM tbl GROUP BY fld1, fld2
SELECT fld1, fld2, count(*) FROM tbl GROUP BY fld1, fld2 LIMIT 1
SELECT fld1, fld2, count(*) FROM tbl WHERE fld1 = 1 GROUP BY fld1, fld2
SELECT fld1, count(*) FROM tbl GROUP BY fld1 having count(*) > 1
SELECT fld1, fld2, sum(fld3) FROM tbl WHERE fld1 = 1 GROUP BY fld1, fld2
SELECT fld1, fld2, sum(fld3) FROM tbl WHERE fld1 = 1 GROUP BY fld1, fld2 having count(*) > 1
SELECT fld, count(fld) FROM tbl GROUP BY fld
SELECT fld1, count(fld1) FROM tbl WHERE fld2=1 GROUP BY fld1