This topic has not yet been rated - Rate this topic

TopSum

SQL Server 2005

Returns, in order of decreasing rank, the top-most rows of a table whose cumulative total is at least a specified value.

Syntax


TopSum(<table expression>, <rank expression>, <sum>)

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 TopSum 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 total that is specified by the <sum> argument. TopSum returns the smallest number of elements possible while still meeting the specified sum value.

Examples

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

Product name	Unit sales
Apples	1200
Kiwi fruit	500
Oranges	1500
Lemons	750

In this example, the following function:

TopSum([Products], [Unit Sales], 2500)

returns the following table:

Product name	Unit sales
Oranges	1500
Apples	1200

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