Skip Headers

Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
 Home Book List Contents Index Master Index Feedback

Selecting Data, 5 of 13

## Limiting to the Top or Bottom Values

You can set the dimension values that are currently in status to the top or bottom performers based on a criterion represented as an expression. The simplified syntax for using the `LIMIT` command in this way is shown below:

```LIMIT dimension TO [BOTTOM|TOP] n BASEDON expression

```

You can also set the dimension values that are currently in status to the top or bottom performers, by percentage, based on a criterion represented as an expression. The simplified syntax for using the `LIMIT` command in this way is shown below.

```LIMIT dimension TO [BOTTOM|TOP] percent PERCENTOF expression

```

This construction sorts values based on their contribution, by percentage, to an expression and then places the identified values in status.

It can happen that the last item in status, based on a `PERCENTOF` criterion, is one of a number of dimension values having the same associated criterion value. In this case, `LIMIT` includes all dimension values with that criterion value in the resulting status, even when that causes the total of the criterion value to far exceed the specified percentage.

 Note: Do not use a criterion expression that changes its own value.

#### Example 6-2 Limiting to the Top or Bottom Values Based on Criterion

Suppose the status list is sorted in descending order according to the values of `sales`, and only the top two performers are kept in status. Here the `TOP` and `BASEDON` keywords are used to limit the status of a dimension, using the values of a variable as a criterion.

```LIMIT product TO 'SPORTSWEAR'
LIMIT month TO 'JUL96'
LIMIT district TO TOP 2 BASEDON sales

```

Suppose that you issue the following `REPORT` command.

```REPORT DOWN district sales

```

The following report is produced, which shows the results of the `LIMIT` commands.

```PRODUCT: SPORTSWEAR
--SALES---
--MONTH---
DISTRICT         JUL96
-------------- ----------
DALLAS         220,416.81
ATLANTA        211,666.14
```

#### Example 6-3 Limiting to the Top or Bottom Values Based on Percentage

Suppose you want to sort products in descending order by the contribution of each product to `TOTAL(sales)` and then add values to the status list, starting from the top, until the cumulative total of `sales` by `product` reaches or exceeds 30 percent of all sales. To limit the dimension in this way, you can use the following command.

```LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product)

```

The following commands produce a report for January through March 2002 of products in the Boston district that reached or exceeded 30 percent of all sales.

```LIMIT month TO 'JAN02' 'FEB02' 'MAR02'
LIMIT district TO 'BOSTON'
LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product)
REPORT sales

```

This output of the report is shown below.

```DISTRICT: BOSTON
-------------SALES--------------
-------------MONTH--------------
PRODUCT          JAN02      FEB02      MAR02
-------------- ---------- ---------- ----------
FOOTWEAR        91,406.82  86,827.32 100,199.46
CANOES          66,013.92  76,083.84  91,748.16
```

Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
 Home Book List Contents Index Master Index Feedback