Managing Result Sets

AMPS allows you to control the results returned by a SOW query by including the following options and header on the query:

Option / Header	Result
top_n (option)	Limits the results returned to the number of messages specified. When a skip_n option is also provided for a subscription, AMPS creates a paginated subscription. Otherwise, this option applies only to the SOW query part of a sow_and_subscribe or sow_and_delta_subscribe command.
skip_n (option)	Skips the number of messages specified before returning results. A command that provides this option must also provide a top_n option.
OrderBy (command header)	Orders the results returned as specified. Requires a comma-separated list of identifiers of the form: /field [ASC | DESC] [ TEXT ] The ASC directive specifies that AMPS sort the results in ascending order (the default). DESC specifies that AMPS sort the results in descending order. The TEXT hint specifies that AMPS will sort the column according to the textual representation of the column. This can be helpful in cases where the column represents a string value, but where some values could be interpreted as numeric values. For example, to sort in descending order by orderDate so that the most recent orders are first, and ascending order by customerName for orders with the same date, you might use a specifier such as: /orderDate DESC, /customerName ASC As another example, the following specifier will sort the orderId field as a string, with the updateTimestamp sorted in descending order for orders with the same orderId. /orderId TEXT, /updateTimestamp DESC If no sort order is specified for an identifier, AMPS defaults to ascending order. If no type hint is specified for an identifier, AMPS defaults to using a mixed-type sort.

For details on how to submit these options with a SOW query, see the documentation for the AMPS client library your application uses.

When replacing a subscription that uses top_n, skip_n, or OrderBy, any of these options specified on the original command must be provided on the replacement command. In other words, sow_and_subscribe command that specifies top_n=10,skip_n=20 must provide both top_n and skip_n on a replacement command.

Paginated SOW and Subscribe

When top_n and skip_n are specified on a sow_and_subscribe command, AMPS creates a paginated subscription. (Both top_n and skip_n must be provided to create a paginated subscription.)

With a paginated subscription, AMPS maintains a list of the set of results for the SOW query, and delivers only results that fall between the first record after the skip_n number and within the number of records specified by the top_n number. This allows applications that only need a subset of the results returned by a filter to work with only those results. This is commonly used for interactive applications, where a user interface shows a small number of records at a time in the interface.

When the subscription specifies an OrderBy, that header specifies the order in which records are sorted within the paginated subscription. If no OrderBy is specified, the results are sorted by the SowKey generated by AMPS (effectively, an arbitrary but stable order).

From a subscriber point of view, paginated subscriptions behave as though only the messages in the pagination window are present in AMPS. For example, when out-of-focus notifications are enabled and a message in the topic is deleted, subscribers receive an oof notification only if the deleted message was in the pagination window. Likewise, if a message that was previously in the pagination window falls outside of the window due to an insert or delete, the message that is now outside of the window will be out of focus, and will generate an oof notification.

For example, consider the following topic in the SOW, where the topic uses the /id field as a key.

With a top_n of 2, a skip_n of 1, and an OrderBy of /id, the results for the subscription will include the records with id of 2 and id of 5.

Now a new message is published with an id of 4, as shown below:

Pagination window after update

Since the new message falls within the pagination window, the message is published to the subscriber. Given that the message with the id of 5 is no longer within the pagination window, the subscriber will receive an oof message for the message with an id of 5 if the subscriber has requested out-of-focus notifications.

While a paginated subscription is active, AMPS maintains a list of the messages that match the subscription in memory (but does not, as of version 5.3.2, maintain the entire sorted result set in memory). For efficiency, when more than one subscription uses the same topic, these subscriptions will use the same result set in memory. The memory used counts as part of the configured MessageMemoryLimit. Each connection that uses the result set is counted as consuming a portion of the memory retained. For example, if 5 connections use the same result set, each of those connections is counted as using 1/5 of the memory for the result set.

In addition, each paginated subscription requires that AMPS maintain state for the window for that subscription: this memory is not shared and is counted for that client.

Aggregated SOW Queries

AMPS provides the ability to aggregate the results of a SOW query. The results of an aggregated SOW query are the same as the results of querying a View with the same definition.

To request an aggregated SOW query, provide the grouping and projection options with the sow query.

Option	Description
grouping=[keys]	For use with aggregated SOW queries. The format of this option is a comma-delimited list of XPath identifiers within brackets. For example, to aggregate entries based on their /description (producing one record in the aggregation for each distinct value in /description), you would use the following option: grouping=[/description] When this option is provided, a projection must also be provided. When the topic has History enabled, this option can be used with a bookmark to aggregate the historical state of the SOW.
projection=[fields]	For use with aggregated SOW queries. Specifies a comma-delimited set of fields to project, within brackets. Each entry has the format described in the AMPS User Guide. This option must contain an entry for every field in the aggregated message. If there is no entry for a field in this option, that field will not appear in the aggregated message, even if the field is in the underlying message. There is no default for this option. When this option is provided, a grouping must also be provided. When the topic has History enabled, this option can be used with a bookmark to aggregate the historical state of the SOW.