Analytics SAQL Reference

SAQL Overview
SAQL Examples
SAQL Basic Elements
SAQL Operators
SAQL Statements
load
filter
foreach
group and cogroup
union
order
limit
offset
SAQL Functions
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

order

Sorts in ascending or descending order on one or more fields.

Syntax

1result = order rows by field [ asc | desc ];
2result = order rows by (field [ asc | desc ], field [ asc | desc ]);
3result = order rows by field [ asc | desc ] nulls [first | last];

asc or desc specifies whether the results are ordered in ascending (asc) or descending (desc) order. The default order is ascending.

Usage

The order statement isn’t applied to the whole set. The order statement operates on rows individually.

You can use the order statement with ungrouped data. You can also use the order statement to specify order within a group or to sort grouped data by an aggregated value.

By default, nulls are sorted last when sorting in ascending order and first when sorting in descending order. You can change the ordering of nulls using nulls [first | last].

Applying labels to dimension values in the XMD changes the displayed values, but doesn’t change the sort order.

Note

Example

q = order q by 'count' desc;

Example

To order a stream by multiple fields, use this syntax: 
1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2b = group a by (year, month);
3c = foreach b generate year as year, month as month;
4d = order c by (year desc, month desc);

Example

You can order a cogrouped stream before a foreach statement:
1a = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ"; 
2b = load "0Fayy000000002qCAA/0Fbyy000000002WCAQ"; 
3c = cogroup a by year, b by year; 
4c = order c by a.airlineName; 
5c = foreach c generate year as year;

Example

By default, nulls are sorted first when sorting in descending order. To change the null sort order to last, use this syntax: 
1q = order q by last_shipping_cost desc nulls last;

Example

You can’t reference a preprojection ID in a postprojection order operation. (Projection is another term for a foreach operation.) This code throws an error: 
1q = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2q = group q by 'FirstName';
3q = foreach q generate sum('mea_mm10M') as 'sum_mm10M';
4q = order q by 'FirstName' desc;
This code is valid:
1q = load "0Fbxx000000002qCAA/0Fcxx000000002WCAQ";
2q = group q by 'FirstName';
3q = foreach q generate 'FirstName' as 'User_FirstName', sum('mea_mm10M') as 'sum_mm10M';
4q = order q by 'User_FirstName' desc;