Order and limit results

You can specify the order of rows in your response using the ORDER BY clause, which consists of one or more comma-separated sequences of:

FieldName ('ASC' | 'DESC')?

If you don't specify ASC or DESC after a field name, the Reporting API defaults to ASC.

The following ORDER BY clause sorts a performance report by descending impressions and ascending product offer IDs:

ORDER BY metrics.impressions DESC, segments.offer_id ASC

ORDER BY is not allowed on non-selected metrics or non-selected segments.

Limit the number of results

You can limit the total number of results returned using the LIMIT clause. Combining this with results ordering, you can produce "top N" reports, such as a report containing the five products with the highest impressions over the last 30 days:

Example

SELECT
  segments.offer_id,
  metrics.impressions
FROM MerchantPerformanceView
WHERE segments.date BETWEEN '2020-12-01' AND '2020-12-31'
ORDER BY metrics.impressions DESC
LIMIT 5;

Filtering (WHERE clause) is not allowed on metrics without selecting them.