Sorting Results — ORDER BY

The default sort order of ORDER BY is ascending — ASC. For numeric columns, ascending means smallest to largest. For text columns, ascending means A to Z (lexicographic order based on the column's collation). For date and timestamp columns, ascending means oldest to newest. Writing ORDER BY column is identical to writing ORDER BY column ASC — the ASC keyword is optional but explicit and improves readability.

To reverse the sort order, add DESC (descending) after the column name: ORDER BY unit_price DESC sorts from highest to lowest price. ORDER BY order_date DESC sorts from newest to oldest — the standard for any "most recent first" dashboard. ORDER BY last_name DESC sorts Z to A.

In a multi-column ORDER BY, each column has its own independent direction. ORDER BY category ASC, unit_price DESC sorts categories A to Z, and within each category sorts prices from highest to lowest. The ASC/DESC keyword applies to the immediately preceding column only.

NULL handling in ORDER BY differs between database systems. In PostgreSQL and DuckDB, NULLs sort last in ascending order (they appear after all non-null values) and first in descending order (they appear before all non-null values). MySQL does the opposite: NULLs sort first in ascending order and last in descending order.

In PostgreSQL and DuckDB, you can explicitly control NULL position using NULLS FIRST or NULLS LAST modifiers: ORDER BY delivery_date ASC NULLS LAST places nulls at the end even in ascending order. ORDER BY delivery_date DESC NULLS LAST places nulls at the end even in descending order — useful when you want real dates first and undelivered orders (NULL delivery_date) at the bottom regardless of sort direction.

MySQL does not support NULLS FIRST / NULLS LAST syntax. The workaround is to sort by an IS NULL expression: ORDER BY (delivery_date IS NULL) ASC, delivery_date ASC. The IS NULL expression returns 0 for non-null values and 1 for null values, so sorting ASC on it puts non-nulls (0) before nulls (1). This technique works across all databases and is useful when writing cross-database compatible SQL.

Yes, you can use a SELECT alias in ORDER BY, and this is one of the important differences between ORDER BY and WHERE. The reason is SQL's logical execution order: FROM runs first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY last. Because ORDER BY executes after SELECT, the aliases defined in SELECT already exist by the time ORDER BY is evaluated. This makes ORDER BY alias_name perfectly valid.

For example: SELECT unit_price - cost_price AS margin FROM products ORDER BY margin DESC works correctly. The alias 'margin' is defined in SELECT, and ORDER BY can reference it because it runs after SELECT. This is cleaner than repeating the full expression: ORDER BY (unit_price - cost_price) DESC — both produce identical results, but the alias version is more readable especially for complex expressions.

By contrast, you cannot use a SELECT alias in WHERE because WHERE runs before SELECT — the alias does not exist yet. WHERE margin > 100 would throw an error. You must repeat the full expression: WHERE (unit_price - cost_price) > 100. This asymmetry between WHERE (cannot use aliases) and ORDER BY (can use aliases) confuses many beginners, but it follows directly from the logical execution order.

When you ORDER BY multiple columns separated by commas, the database sorts by the first column. Among rows that have identical values in the first column — ties — it sorts by the second column. Among remaining ties, it sorts by the third column, and so on. Each column independently can be ASC or DESC.

A practical example: ORDER BY category ASC, unit_price DESC on a products table. The database first sorts all products alphabetically by category — Beverages, Dairy, Fruits, Household, Packaged Food, Personal Care, Staples, Vegetables. Within each category, products are sorted from most expensive to cheapest. A Beverages product at ₹270 appears before a Beverages product at ₹115, but both appear before any Dairy product.

Multi-column ORDER BY is essential for any report where items are grouped by one dimension and ranked within those groups. Common real-world uses: ORDER BY store_id ASC, revenue DESC (each store's top products), ORDER BY department ASC, salary DESC (highest earner in each department), ORDER BY order_date DESC, order_id DESC (most recent orders, ties broken by ID). The second column only ever affects the relative order of rows that tied on the first column — if no ties exist on the first column, the second column has no effect.

No. When multiple rows have identical values in all ORDER BY columns — a complete tie — the database can return them in any order, and that order may change between query executions. SQL does not guarantee a deterministic result for tied rows unless you provide enough sort columns to uniquely distinguish every row.

This matters for pagination. If you use ORDER BY order_date DESC LIMIT 10 to get the first page of results, and then ORDER BY order_date DESC LIMIT 10 OFFSET 10 for the second page, any orders placed on the same date might appear on the wrong page or appear on both pages if the database reorders the ties between the two queries. The result is a silent data integrity issue in pagination systems.

The fix is always to include a unique column as the final tiebreaker: ORDER BY order_date DESC, order_id DESC. Since order_id is the primary key and unique for every row, there are no ties and the order is completely deterministic. Every paginated query should end with the primary key as the final sort column to guarantee consistent, non-overlapping pages. This practice is called a stable sort and is standard in any production API that supports pagination.

Cause: You referenced a name in ORDER BY that does not exist as a column in the table or as an alias in SELECT. This happens when you misspell an alias, when you try to sort by a column that exists in the table but is not included in your SELECT list (in some databases), or when you confuse a column name with a variable or calculated name.

Fix: Check that the alias name in ORDER BY exactly matches the alias defined in SELECT — including case if your database is case-sensitive for identifiers. If you want to sort by a calculated value that is not in SELECT, either add it to SELECT with an alias or repeat the full expression in ORDER BY: ORDER BY (unit_price - cost_price) DESC. In PostgreSQL you can sort by a column even if it is not in SELECT, but the column name must exactly match a column in the table.

Cause: You sorted by a column that has duplicate values (order_date in this example), and you did not include a tiebreaker column. When multiple rows have identical sort values, the database is free to return them in any order — and that order can change between executions depending on internal state, parallelism, and storage layout changes. This is not a bug — it is undefined behaviour that the database is permitted to resolve arbitrarily.

Fix: Always add a unique column as the final tiebreaker in ORDER BY. The primary key is almost always the right choice: ORDER BY order_date DESC, order_id DESC. With order_id as the final sort key, every row has a unique position in the result and the order is guaranteed to be identical on every execution. This is especially important for paginated results where inconsistent ordering causes rows to be skipped or duplicated across pages.

Cause: ORDER BY must be the last clause in a SELECT statement (before any LIMIT). Placing it before WHERE, before GROUP BY, or before HAVING causes a syntax error. This typically happens when editing a query and accidentally moving the ORDER BY block above another clause.

Fix: The correct clause order is: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT. ORDER BY must come after all filtering and grouping clauses. If you are building a query incrementally, always add ORDER BY as the second-to-last clause, followed by LIMIT as the absolute last. In most SQL editors, syntax highlighting will show ORDER BY in the wrong position as a different colour — look for the visual indicator before running.

Cause: You are using MySQL which sorts NULLs first in ascending order — the opposite of PostgreSQL. If your development database is PostgreSQL and your production database is MySQL (or vice versa), NULL sort behaviour will differ between environments. Queries that appear correct in development produce different output in production.

Fix: Use the database-appropriate syntax to control NULL position explicitly. In PostgreSQL/DuckDB: ORDER BY column ASC NULLS LAST. In MySQL (no NULLS LAST support): ORDER BY (column IS NULL) ASC, column ASC — the IS NULL expression produces 0 for non-null and 1 for null, so ascending sort puts non-nulls first. For cross-database compatible code, always use the CASE-based workaround: ORDER BY CASE WHEN column IS NULL THEN 1 ELSE 0 END ASC, column ASC — this works identically on PostgreSQL and MySQL.

Cause: Sorting requires the database to process every row in the result set before returning any rows. Without a WHERE clause filtering the data first, the database must sort all rows in the table. On a table with millions of rows, a full sort of the entire table can take minutes — especially if the result does not fit in memory and requires a disk-based sort with temporary files.

Fix: Three solutions in order of preference. First, add a WHERE clause to filter the data before sorting — sorting 10,000 rows is orders of magnitude faster than sorting 50 million. Second, add LIMIT — ORDER BY column DESC LIMIT 10 lets the database use a heap sort optimisation that only tracks the top 10 rows, far cheaper than a full sort. Third, add an index on the sort column — if the column is already indexed in the required order, the database can read the index in order without sorting at all. Always check the query plan with EXPLAIN to understand which of these the database is doing.

The FreshMart procurement team wants a product report for their weekly review. Write a query that returns all in-stock products sorted first by category alphabetically, then within each category by unit_price from highest to lowest. Show product_name, category, brand, unit_price, and a calculated column called margin that is unit_price minus cost_price. The report should only include products where the margin is greater than ₹30.