SORT command

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 [byhierarchy] [bycriterion...]

where:

byhierarchy is an optional phrase that uses a parent relation to arrange the order of values in the current status list of a hierarchical dimension or its dimension surrogate, or to assign values to a valueset, based on family relationships within the hierarchy. You can include only one byhierarchy phrase in a SORT statement. It must be the first phrase in a SORT statement.

HIERARCHY parent-relation [INVERT] [DEPTH n] [SORTORPHANS]
bycriterion uses an explicit criterion to arrange the order of values in the current status list of a dimension or its dimension surrogate, or to assign values to a valueset. You can include as many bycriterion phrases as you want in a SORT statement.

{A|D} [NAFIRST] criterion

Arguments

dimension

A text expression whose value is the name of a dimension, a dimension surrogate, or a valueset.

HIERARCHY

Specifies that Oracle OLAP is to sort dimension values based on the values position in parentrel.

parent-relation

Specifies the name of a child-parent self-relation for dimension. For each dimension value, the relation holds another value of the dimension which is its parent dimension value (the one immediately above it in a given hierarchy). This parent relation can have more than one dimension.

Tip:

You can specify a QDR of parent-relation to specify a single value.

INVERT

Indicates that Oracle OLAP places the children in the hierarchy before their parents. (By default, children are placed after their parents.)

DEPTH n

Specifies the number of generations down from the top of the hierarchy that Oracle OLAP should place into status and. there, for the values that are included in the result. The default value of n is 99. When you do not want any values in the result (that is, when you want a NULL status), specify -1 for n. When you only want the top of the hierarchy in status (that is, those dimension values that do not have parents), specify 0 (zero).

SORTORPHANS

Specifies that all first cousins whose parents are not in status are sorted together. By default, Oracle OLAP preserves the hierarchical structure when sorting children even when their parents are not in status.

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).

NAFIRST

Specifies that NA values are to placed first in the sort rather than last.

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 statement 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 for Textual Data

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 Using a Relation as a Criterion

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 Using a Relation as the Criterion".

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 Using a Relation as the Criterion

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 statement, 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 statement.

REPORT DOWN district HEADING 'Region' region.district sales

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

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 statement, 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 statement.

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 a MAINTAIN statement 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>

Example 21-43 Sorting by Hierarchy

Assume that your analytic workspace has two dimensions (geog and time), one relation (geogparent) and one variable (sales) with the following definitions.

DEFINE GEOG DIMENSION TEXT
DEFINE TIME DIMENSION TEXT
DEFINE GEOGPARENT RELATION GEOG <GEOG>
DEFINE SALES VARIABLE INTEGER <TIME GEOG>

If you issue a REPORT statement for sales when all of the values of geog are in status, Oracle OLAP produces the following report. This report displays the values for geog in the order in which they were added to the analytic workspace.

REPORT sales
               --------SALES--------
               --------TIME---------
GEOG              2004       2005
-------------- ---------- ----------
USA                 1,300         NA
Massachusetts       3,881         NA
Florida             3,479         NA
Boston              2,644         NA
Orlando             4,398         NA
Miami               3,294         NA
Pembroke            4,268         NA
California          1,899         NA
Texas               2,115         NA
Los Angeles         2,394         NA
San Francisco       1,334         NA
Dallas                839         NA
Houston               997         NA

However, assume that you issue a SORT statement to sort the values of geog by (1) geogparent and (2) descending by sales. After this sort if you issue a REPORT statement for sales, Oracle OLAP produces the following report. This report displays the values for geog with the states in descending order by sales, but with the cities of each state under the appropriate state.

SORT geog HIERARCHY geogparent D sales
REPORT SALES
 
               --------SALES--------
               --------TIME---------
GEOG              2004       2005
-------------- ---------- ----------
USA                 1,300         NA
Massachusetts       3,881         NA
Pembroke            4,268         NA
Boston              2,644         NA
Florida             3,479         NA
Orlando             4,398         NA
Miami               3,294         NA
Texas               2,115         NA
Houston               997         NA
Dallas                839         NA
California          1,899         NA
Los Angeles         2,394         NA
San Francisco       1,334         NA

Example 21-44 Sorting Orphans of a Hierarchy

Assume that you have the same objects described in Example 21-43, "Sorting by Hierarchy". Assume also that the states of Florida and Massachusetts are not in status.

When you include the SORTORPHANS keyword in your SORT statement, the cities in Massachusetts and Florida are sorted together.

SORT geog HIERARCHY geogparent SORTORPHANS D sales
REPORT sales
               ------------------SALES------------------
               ------------------TIME-------------------
GEOG                   2004                 2005
-------------- -------------------- --------------------
USA                           1,300                   NA
Orlando                       4,398                   NA
Pembroke                      4,268                   NA
Miami                         3,294                   NA
Boston                        2,644                   NA
Texas                         2,115                   NA
Houston                         997                   NA
Dallas                          839                   NA
California                    1,899                   NA
Los Angeles                   2,394                   NA
San Francisco                 1,334                   NA

However, if you exclude the SORTORPHANS keyword, Massachusetts cities and Florida cities are sorted separately.

LIMIT geog COMPLEMENT 'Florida' 'Massachusetts'
SORT geog HIERARCHY geogparent D sales
REPORT SALES
 
              --------SALES--------
               --------TIME---------
GEOG              2004       2005
-------------- ---------- ----------
USA                 1,300         NA
Pembroke            4,268         NA
Boston              2,644         NA
Orlando             4,398         NA
Miami               3,294         NA
Texas               2,115         NA
Houston               997         NA
Dallas                839         NA
California          1,899         NA
Los Angeles         2,394         NA
San Francisco       1,334         NA