SORT

The SORT command arranges the order of values in the current status list of a dimension or a dimension surrogate, or in a valueset.

Syntax

SORT dimension {A|D} criterion1 [{A|D} criterionN]

Arguments

dimension: A text expression whose value is the name of a dimension, a dimension surrogate, or a valueset.
A
D: The order in which the values are to be sorted. A means ascending order (alphabetical when the sorting criterion is TEXT, ID, or a relation), and D means descending order (reverse alphabetical when the sorting criterion is TEXT, ID or a relation).
criterion: The expression to be used as a sorting criterion. Each criterion must be dimensioned by dimension. The first expression is the major sorting criterion. When the expression is multidimensional, SORT uses the first value in status for all dimensions other than the dimension being sorted. You cannot use a valueset as the sorting criterion.

Notes

Sorting a Dimension and a Valueset

When Oracle OLAP sorts a dimension, it sorts the temporary status list of a dimension, not the data dimensioned by it. Since many OLAP DML statements operate on data according to the current status of its dimensions, sorting a dimension appears to have the effect of sorting data. A dimension and any dimension surrogates for it share the same status. Therefore, a SORT command on a dimension or any of its surrogates sorts them all.

When Oracle OLAP sorts a valueset, it sorts the actual values within the valueset. When you execute UPDATE and COMMIT commands after sorting a valueset, the values in the valueset are stored in that sorted order.

Sorting Alphabetically

To sort a TEXT or ID dimension or its valueset in alphabetical order, use the dimension itself as the sorting criterion.

SORT district A district

Sort Order

The sort order for textual data in an alphabetical sort is controlled by the NLS_SORT option.

Sorting a Time Dimension

The values of dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR are stored internally as numbers. Therefore, when you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in ascending order, with the dimension itself as the sorting criterion, then the values in the status list or valueset are placed in chronological order. When you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in descending order, then the values are placed in reverse chronological order.

Sorting Based on a Relation

When you use a relation as your sorting criterion, then the sorting is done alphabetically; that is, the dimension or valueset is sorted according to an alphabetical list of the related dimension values. To use a relation as the sorting criterion and keep the related dimension values in their original order, you must use the following expression as your sorting criterion See Example 21-40, "Sorting Based on a Relation".

CONVERT(relation, INTEGER)

Sorting Conjoint Dimensions

You can sort a conjoint dimension or its valueset by criteria dimensioned by either the conjoint dimension itself or by one of its base dimensions.

Sorting Concat Dimensions

You can sort a concat dimension or its valueset by criteria dimensioned by either the concat dimension itself or by one of its component dimensions. See Example 21-41, "Sorting Based on a Concat" and Example 21-42, "Sorting Based on a Component".

Sorting a Worksheet

You cannot use a worksheet as a sort criterion. You must first use CONVERT to specify the data type to which values of the worksheet should be converted.

Examples

Example 21-40 Sorting Based on a Relation

This example sorts districts according to their unit sales of tents for July 1996. They are sorted first by the region to which they belong and then in descending order of dollar sales. Notice that in the following SORT command, a relation is used as the primary sorting criterion. This means that the districts are sorted by regions listed alphabetically.

LIMIT month TO 'Jul96'
LIMIT product TO 'Tents'
SORT district A Region.District D sales

Assume you issue the following REPORT command.

REPORT DOWN district HEADING 'Region' region.district sales

The preceding statement produces the following report that reflects the work of the SORT command.

PRODUCT: Tents
               --------MONTH--------
               --------JUL96--------
DISTRICT         Region     SALES
-------------- ---------- ----------
Dallas         Central    154,914.23
Chicago        Central     79,934.42
Atlanta        East       140,711.00
Boston         East        93,972.49
Seattle        West       123,700.17
Denver         West       100,413.49

In the following SORT command, CONVERT is used to keep the regions in their original order.

SORT district A CONVERT(region.district INTEGER) D sales

Assume that you issue the following REPORT statement.

REPORT DOWN district HEADING 'Region' region.district sales

The preceding statement produces the following report that reflects the work of the last SORT command.

PRODUCT: Tents
               --------MONTH--------
               --------JUL96--------
DISTRICT         Region     SALES
-------------- ---------- ----------
Atlanta        East       140,711.00
Boston         East        93,972.49
Dallas         Central    154,914.23
Chicago        Central     79,934.42
Seattle        West       123,700.17
Denver         West       100,413.49

When you want the dimension to keep the sorted order of its values permanently, use the MAINTAIN command after you sort the dimension.

SORT district A district
MAINTAIN district MOVE VALUES(district) FIRST

Example 21-41 Sorting Based on a Concat

The following statements sort the concat dimension reg.dist.ccdim in ascending order based on all of its values and report the result.

sort reg.dist.ccdim d reg.dist.ccdim
report reg.dist.ccdim

The preceding statement produces the following results.

REG.DIST.CCDIM
--------------------
<Region: West>
<Region: East>
<Region: Central>
<District: Seattle>
<District: Denver>
<District: Dallas>
<District: Chicago>
<District: Boston>
<District: Atlanta>

The following statements sort the concat dimension reg.dist.ccdim in ascending order based on all of its values and report the result.

SORT reg.dist.ccdim A reg.dist.ccdim
REPORT reg.dist.ccdim

The preceding statement produces the following results.

REG.DIST.CCDIM
--------------------
<District: Atlanta>
<District: Boston>
<District: Chicago>
<District: Dallas>
<District: Denver>
<District: Seattle>
<Region: Central>
<Region: East>
<Region: West>

Example 21-42 Sorting Based on a Component

The following statements sort the concat dimension reg.dist.ccdim in ascending order based on the values of one of its base dimensions and in descending order based on the values of its other base dimension, and report the result.

SORT reg.dist.ccdim A region D district
REPORT reg.dist.ccdim

The preceding statement produces the following results.

REG.DIST.CCDIM
--------------------
<REGION: CENTRAL>
<REGION: EAST>
<REGION: WEST>
<DISTRICT: SEATTLE>
<DISTRICT: DENVER>
<DISTRICT: DALLAS>
<DISTRICT: CHICAGO>
<DISTRICT: BOSTON>
<DISTRICT: ATLANTA>