Version v2 of the Content API is scheduled for sunset on September 30th, 2021. Onboarding to v2 ended on April 30, 2021. To avoid disruptions with your integration, please migrate to v2.1 as soon as possible.

For more information, see Migrating to v2.1 and this blog post.

Ordering and Limiting Results

Ordering 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.

Limiting 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.