ORDER BY

Definition

ORDER BY sorts the rows of a result set. Without it, SQL makes no promise about the order rows come back in — the engine can return them in whatever order is convenient (file order, index order, join order), and that order can change between runs. ORDER BY is the only clause that guarantees a specific order.

Syntax:

sql
SELECT column1, column2, ...
FROM table_name
ORDER BY sort_expression [ASC | DESC], ...;

ASC (ascending) is the default — you almost never need to write it explicitly. DESC reverses the direction.

Running Example

sql
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(30),
  price NUMERIC(10,2),
  rating NUMERIC(2,1)
);

INSERT INTO products (id, name, category, price, rating) VALUES
 (1, 'Wireless Mouse', 'Electronics', 599.00, 4.2),
 (2, 'Bluetooth Speaker', 'Electronics', 1499.00, 4.5),
 (3, 'Desk Lamp', 'Home', 899.00, 4.0),
 (4, 'Yoga Mat', 'Sports', 499.00, 4.6),
 (5, 'Notebook Set', 'Stationery', 199.00, 3.8);

Sort cheapest to most expensive:

sql
SELECT name, price
FROM products
ORDER BY price;   -- ASC is implied

Result:

 name               | price
--------------------+--------
 Notebook Set       | 199.00
 Yoga Mat           | 499.00
 Wireless Mouse     | 599.00
 Desk Lamp          | 899.00
 Bluetooth Speaker  | 1499.00

Most expensive first:

sql
SELECT name, price FROM products ORDER BY price DESC;

Sorting by Position or Expression

SQL lets you order by the ordinal position of a column in the SELECT list, not just its name:

sql
SELECT name, price FROM products ORDER BY 2 DESC;  -- sorts by price

This works but is discouraged: if someone reorders or adds a column to the SELECT list, the meaning of ORDER BY 2 silently changes, producing a hard-to-spot bug. Prefer naming the column or its alias.

You can also order by an expression or an alias that isn't even in the SELECT list:

sql
SELECT name, price, price * 0.9 AS discounted_price
FROM products
ORDER BY discounted_price;

-- or without an alias in scope, order by the raw expression:
SELECT name FROM products ORDER BY LENGTH(name);

Key Takeaways

• Q: What order does SQL return rows in if I omit ORDER BY?

A: Unspecified — never rely on it, even if it "looks" consistent in testing.

• Q: Is ASC or DESC the default?

A: ASC.

• Q: Why avoid `ORDER BY 2`?

A: It's positional and breaks silently when the SELECT list changes; column names/aliases are self-documenting and safer.