SELECT DISTINCT FROM <model > (DMX)

  • Article

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

Help and Information

Getting SQL Server 2005 Assistance