Orders by Highest Amount — ORDER BY in SQL

The problem

Brightlane's finance team is preparing an order-size report for an upcoming board presentation.

Write a query to return each order's ID and total amount, ranked from largest order to smallest.

Assumptions:

The orders table contains every order Brightlane has ever recorded.
When two orders share the same total_amount, the order with the lower id should appear first.

Output:

One row per order, with columns id and total_amount, sorted by total_amount descending, then by id ascending.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  total_amount
FROM
  orders
ORDER BY
  total_amount DESC,
  id

The shape

DESC on total_amount puts the biggest orders on top — exactly what a board presentation needs — and an ascending id tiebreaker keeps orders of equal size in a stable, reproducible order.

Clause by clause

SELECT id, total_amount returns the two columns the finance team needs for the order-size report.
FROM orders reads the full order history. There's no filter; every order is in scope.
ORDER BY total_amount DESC, id sorts descending by order size, then breaks ties by id ascending. Each sort key carries its own direction; the DESC here applies only to the first key, and the second key defaults to ascending.

Why this and not `ORDER BY total_amount DESC`

Without the id tiebreaker, any two orders that happen to share a total_amount come back in whatever order PostgreSQL finds convenient, and that order can drift between runs as the data changes or the planner picks a different strategy. The presentation might look the same the day it's exported and different a week later, for no reason the analyst can explain. Adding id as a tiebreaker fixes the order of tied rows so the report is reproducible — every export sorts the same way.

You practiced ranking rows from largest to smallest with ORDER BY ... DESC and a deterministic tiebreaker. The 'rank biggest first, break ties by ID' pattern is the everyday shape of any leaderboard, top-N report, or revenue ranking.

Return each order's ID and total amount, ranked from largest order to smallest

The shape

Clause by clause

Why this and not `ORDER BY total_amount DESC`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not ORDER BY total_amount DESC

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `ORDER BY total_amount DESC`