This topic has not yet been rated - Rate this topic

TopPercent

SQL Server 2005

The TopPercent function returns, in order of decreasing rank, the top-most rows of a table whose cumulative total is at least a specified percentage.

Syntax


TopPercent(<table expression>, <rank expression>, <percent>)

Applies To

An expression that returns a table, such as a <table column reference>, or a function that returns a table.

Return Type

Remarks

The TopPercent function returns the top-most rows in decreasing order of rank based on the evaluated value of the <rank expression> argument for each row, such that the sum of the <rank expression> values is at least the given percentage that is specified by the <percent> argument. TopPercent returns the smallest number of elements possible while still meeting the specified percent value.

Examples

For example, assume that a table column named Products contains the following data:

Product name	Unit sales
Apples	30
Kiwi fruit	10
Oranges	40
Lemons	20

In this example, the following function:

TopPercent([Products], [Unit Sales], 60)

returns the following table:

Product name	Unit sales
Oranges	40
Apples	30

Note that Apples is selected instead of Lemons.

Reference

Data Mining Extensions (DMX) Function Reference
Functions (DMX)
Mapping Functions to Query Types (DMX)

Help and Information

Getting SQL Server 2005 Assistance

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD