Sort and page through rows
Use ORDER BY to choose the order, LIMIT to choose the page size, and OFFSET to skip earlier rows.
Before you run it: Choose a deliberate sort order when paging. LIMIT alone does not describe which rows come first.
- Order by amountSort the qualifying orders in descending amount order.
- Skip the requested offsetMove past rows already assigned to earlier pages.
- Return up to the limitInspect the page and use a stable tie-breaker for repeated amounts.
Follow the example
- Load the linked order sample.
- Run the query for the three largest amounts.
- Run the second example to skip ten rows.
- Check the order and result count.
Supported operations
ORDER BY (ascending or descending), LIMIT, and OFFSET all execute server-side through the SQL translator - the engine's Sort operator is wired through. Sorting and pagination happen in the engine, not your app.
Find the three largest amounts
First register and load the orders sample: shop.orders has id, customer_id, amount_cents and status. Use its three sample rows for the results shown here.
# ORDER BY + LIMIT + OFFSET all execute server-side.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT id, amount_cents FROM shop.orders WHERE amount_cents > 0 ORDER BY amount_cents DESC LIMIT 3"
}'Choose a page of results
Page N skips (N-1) × page size rows. The sample amounts are distinct. In real data, add a unique tie-breaker such as id to ORDER BY when amounts can be equal. Inserts or deletes between requests can still shift offset-based pages.
# Page 2 (rows 11-20): ORDER BY + LIMIT + OFFSET.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT id, amount_cents FROM shop.orders ORDER BY amount_cents DESC LIMIT 10 OFFSET 10"
}'Expected result
The first request returns all three sample orders, sorted by amount. The second uses OFFSET 10, so it returns no rows with this three-row sample. Add more rows before testing later pages.