The ‘Select’ Regular Statement

This article will cover the syntax of the ‘Select’ regular script statement. 

This statement is used to pull data from a database using standard SQL syntax.

Statement syntax

Copy code 


Select [distinct | top n [percent]]
Fieldlist
From tablelist
Where criterion
Group by fieldlist 
Having criterion
Order by fieldlist [asc | desc]

Select statements can include join or union SQL statements.

DistinctUsing the ‘distinct’ keyword ensures that only unique records are loaded. If any duplicate rows exist, only the first record will be loaded.
Top n [percent]Returns only the top number of records or percent of data. The order of records is to be specified using the ‘order by’ statement.
FieldlistA comma separated list of fields to be loaded.
TablelistThe ‘from’ keyword is followed by the reference to the database tables.
Where‘Where’ is used to restrict which records are loaded. Logical criteria should be specified after the ‘where’ keyword. A record will be loaded if it meets the condition.
Group by‘Group by’ is required for specifying which fields to aggregate over when an expression is used to create new calculated fields.
Having‘Having’ is used to restrict which groups are loaded. This is only applicable where records have been aggregated into groups. Logical criteria should be specified after the ‘having’ keyword. A group will be loaded if it meets the condition.
Order by‘Order by’ is used to specify how to order the records before they are loaded. Order by should be followed by a comma separated list of fields to be ordered by in order of priority. The ordering can be ascending (use ‘asc’) or descending (use ‘desc’). This is to be specified after each field as required. If none are specified, ‘asc’ is assumed.

Example

A select statement is used to load data from a Google Analytics data connection.

Copy code 


GA_DATA:
SELECT 
	dim_pagePath,
	metric_pageviews,
	metric_uniquePageviews,
	metric_sessionDuration,
	metric_bounces,
	metric_entrances,
	metric_exits
FROM DataFromTemplateQuery
WITH PROPERTIES (
profileId='xyz',
prebuiltQuery='query_parameters'
);

Result

The data is loaded into a table ‘GA_DATA’.