SELECT statement
Selects data from a FeatureBase table.
BNF diagrams
top_clause
select_list
from_clause
where_clause
group_by_clause
having_clause
order_by_clause
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
*
wildcard represents all columns<qualifier>.*
limits the results to all columns based on the specified qualifierexpr
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
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
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.
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