SELECT DISTINCT FROM <model > (DMX)
Returns all possible states for the selected column in the model.
Syntax
SELECT [FLATTENED] DISTINCT [TOP <n>] <expression list> FROM <model>
[WHERE <condition list>][ORDER BY <expression>]
Arguments
- n
Optional. An integer specifying how many rows to return.
- expression list
A comma-separated list of related column identifiers (derived from the model) or expressions.
- model
A model identifier.
- condition list
A condition to restrict the values that are returned from the column list.
- expression
Optional. An expression that returns a scalar value.
Remarks
The SELECT DISTINCT FROM statement only works with a single column or with a set of related columns. This clause does not work with a set of unrelated columns.
The SELECT DISTINCT FROM statement allows you to directly reference a column inside of a nested table. For example:
<model>.<table column reference>.<column reference>
The results of the SELECT DISTINCT FROM <model> statement can vary, depending on the column type. The following table describes the supported column types and the output from the statement.
| Column type | Output |
|---|---|
Discrete |
The unique values in the column. |
Discretized |
The midpoint for each discretized bucket in the column. |
Continuous |
The midpoint for the values in the column. |
Discrete Column Example
The following code sample returns the unique values that exist in the discrete column, Gender.
SELECT DISTINCT [Gender]
FROM [TM Decision Tree]
Discretized Column Example
The following code sample returns the midpoint, maximum, and minimum values for each discretized bucket in the column, Yearly Income.
SELECT DISTINCT [Yearly Income] AS [Bucket Average],
RangeMin([Yearly Income]) AS [Bucket Minimum],
RangeMax([Yearly Income]) AS [Bucket Maximum]
FROM [TM Decision Tree]
Continuous Column Example
The following code sample returns the midpoint, minimum age, and maximum age for all of the values in the column.
SELECT DISTINCT [Age] AS [Midpoint Age],
RangeMin([Age]) AS [Minimum Age],
RangeMax([Age]) AS [Maximum Age]
FROM [TM Decision Tree]
See Also
Reference
SELECT (DMX)
Data Mining Extensions (DMX) Data Manipulation Statements
Data Mining Extensions (DMX) Statement Reference