Skip to main content Skip to complementary content

Select

The selection of fields from an ODBC data source or OLE DB provider is made through standard SQL SELECT statements. However, whether the SELECT statements are accepted depends on the ODBC driver or OLE DB provider used. Use of the SELECT statement requires an open data connection to the source.

Syntax:  

Select [all | distinct | distinctrow | top n [percent] ] fieldlist

From tablelist

[where criterion ]

[group by fieldlist [having criterion ] ]

[order by fieldlist [asc | desc] ]

[ (Inner | Left | Right | Full) join tablename on fieldref = fieldref ]

 

Furthermore, several SELECT statements can sometimes be concatenated into one through the use of a union operator:

selectstatement Union selectstatement

 

The SELECT statement is interpreted by the ODBC driver or OLE DB provider, so deviations from the general SQL syntax might occur depending on the capabilities of the ODBC drivers or OLE DB provider, for example:.

  • as is sometimes not allowed, i.e. aliasname must follow immediately after fieldname.
  • as is sometimes compulsory if an aliasname is used.
  • distinct, as, where, group by, order by, or union is sometimes not supported.
  • The ODBC driver sometimes does not accept all the different quotation marks listed above.
Information noteThis is not a complete description of the SQL SELECT statement! E.g. SELECT statements can be nested, several joins can be made in one SELECT statement, the number of functions allowed in expressions is sometimes very large, etc.

Arguments:  

Arguments
Argument Description
distinct distinct is a predicate used if duplicate combinations of values in the selected fields only should be loaded once.
distinctrow distinctrow is a predicate used if duplicate records in the source table only should be loaded once.
fieldlist fieldlist ::= (*| field ) {, field }

A list of the fields to be selected. Using * as field list indicates all fields in the table.

fieldlist ::= field {, field }

A list of one or more fields, separated by commas.

field ::= ( fieldref | expression ) [as aliasname ]

The expression can e.g. be a numeric or string function based on one or several other fields. Some of the operators and functions usually accepted are: +, -, *, /, & (string concatenation), sum(fieldname), count(fieldname), avg(fieldname)(average), month(fieldname), etc. See the documentation of the ODBC driver for more information.

fieldref ::= [ tablename. ] fieldname

The tablename and the fieldname are text strings identical to what they imply. They must be enclosed by straight double quotation marks if they contain e.g. spaces.

The as clause is used for assigning a new name to the field.
from tablelist ::= table {, table }

The list of tables that the fields are to be selected from.

table ::= tablename [ [as ] aliasname ]

The tablename may or may not be put within quotes.

From

where where is a clause used for stating whether a record should be included in the selection or not.

criterion is a logical expression that can sometimes be very complex. Some of the operators accepted are: numeric operators and functions, =, <> or #(not equal), >, >=, <, <=, and, or, not, exists, some, all, in and also new SELECT statements. See the documentation of the ODBC driver or OLE DB providerfor more information.

group by group by is a clause used for aggregating (group) several records into one. Within one group, for a certain field, all the records must either have the same value, or the field can only be used from within an expression, e.g. as a sum or an average. The expression based on one or several fields is defined in the expression of the field symbol.
having having is a clause used for qualifying groups in a similar manner to how the where clause is used for qualifying records.
order by order by is a clause used for stating the sort order of the resulting table of the SELECT statement.
join join is a qualifier stating if several tables are to be joined together into one. Field names and table names must be put within quotes if they contain blank spaces or letters from the national character sets. When the script is automatically generated by Qlik Sense, the quotation mark used is the one preferred by the ODBC driver or OLE DB provider specified in the data source definition of the data source in the Connect statement.

Example 1:  

SELECT * FROM `Categories`;

Example 2:  

SELECT `Category ID`, `Category Name` FROM `Categories`;

Example 3:  

SELECT `Order ID`, `Product ID`,

`Unit Price` * Quantity * (1-Discount) as NetSales

FROM `Order Details`;

Example 4:  

SELECT `Order Details`.`Order ID`,

Sum(`Order Details`.`Unit Price` * `Order Details`.Quantity) as `Result`

FROM `Order Details`, Orders

where Orders.`Order ID` = `Order Details`.`Order ID`

group by `Order Details`.`Order ID`;

Learn more

 

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!