Products Sorted by Price — ORDER BY in SQL

The problem

Brightlane's product team is preparing a catalogue sorted by price for a buyer presentation.

Write a query to return each product's name and price, sorted from the least expensive to the most expensive.

Assumptions:

The products table contains every product in Brightlane's catalogue.
When two products share the same price, the product with the lower id should appear first.

Output:

One row per product, with columns name and price, sorted by price ascending, 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
  name,
  price
FROM
  products
ORDER BY
  price,
  id

The shape

A two-key ORDER BY — price first, then id as a tiebreaker — turns the catalogue into a deterministic least-to-most-expensive list, even when two products share a price.

Clause by clause

SELECT name, price pulls the two columns the buyer presentation needs. Nothing else from products appears in the output, but every product is still considered for sorting.
FROM products reads the catalogue. Without any WHERE, every row in the table is in play.
ORDER BY price, id sorts by price ascending — ascending is the default, so no ASC is needed — and uses id to break ties. Two products at 12.99 (HDMI Cable 2m and Picture Frame 8x10) come back in the order their id values prescribe, so the result is reproducible run after run.

Why this and not `ORDER BY price` on its own

Without the id tiebreaker, PostgreSQL is free to return the two 12.99 rows in either order, and that order can change between runs as the planner picks different strategies. The query is still correct in the sense that the prices are sorted, but the row sequence inside any group of equal prices is up to the engine. The second sort key locks the order down so the presentation looks the same every time it's exported.

You practiced sorting on a primary key with a secondary tie-breaker. Adding a stable second sort key is the recurring fix for any list whose primary sort can produce ties — it makes the output order deterministic instead of arbitrary.

Return each product's name and price, sorted from the least expensive to the most expensive

The shape

Clause by clause

Why this and not `ORDER BY price` on its own

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 price on its own

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

Why this and not `ORDER BY price` on its own