Maintaining Rows Based on Rank

Windows are also defined based on the rank of a value of a column.

So far, all of the windows you've defined have retained the newest rows (rows that arrived most recently), whether the window was count-based or time-based. You can also define a window policy that retains rows based on the rank of a value of a column, so that the window doesn't necessarily retain the most recent rows.

Follow these steps to create a window that maintains rows with the largest value in a specific column:

Replace the last statement in the Queries tab with the following text (or copy the text from MaintainingStateCCL.txt between the line "-- Maintaining State Code Block 4" and the end of the file):
```
-- Create an unnamed window to track the sum of 
-- the two largest volumes by stock symbol
INSERT INTO FilteredTrades
SELECT *, SUM(Volume)
FROM StockTrades
KEEP 2 LARGEST ROWS BY Volume
WHERE Symbol = 'EBAY'
GROUP BY Symbol;
```
The SELECT clause in this statement includes an aggregate function to calculate the sum of the value in the Volume column for all rows in the implicit window. Note that this calculation appears in the output stream in the column named AvgPrice.

The KEEP clause specifies that the window maintain the two rows with the largest values in the Volume column (the GROUP BY clause specifies that the window keep two rows for each unique value in the Symbol column). When a row arrives on the input stream, Sybase CEP Engine compares the value in the Volume column to the values in the same column of the window for rows with a matching value in the Symbol column. If the value in the new row is higher than either of the values in the window, the new row is added to the window, and the row with the lowest value in the Volume column is removed.

The WHERE clause restricts the output to rows with "EBAY" in the Symbol column.
Make sure that viewers are open and visible for both the input and output stream, and then run the project.
Observe the output stream:

If you examine the value in the AvgPrice column (which is actually the sum of the values in the Volume column in the window), you see that the value never decreases. Remember that this viewer displays the rows as they flow through the output stream, not rows in the window. Sybase CEP Engine produces an output row and calculates the aggregate sum whenever a row arrives on the input stream that matches the filter condition ("EBAY" in the Symbol column). The output row contains the aggregate calculation as well as columns from the input row, not from a row in the window. Sybase CEP Engine calculates the aggregate across the contents of the window, so the sum is based on the two largest values that have arrived since the project started, and does not necessarily include the value in the Volume column of the current row.
Stop the project.

Maintaining Rows Based on Rank

Exercises