SQL Interview Prep · Lesson

Sorting by Expressions and Aliases

Where alias references are legal in ORDER BY and how to sort by computed logic.

Sorting by Expressions and Aliases is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

ORDER BY Is More Flexible Than You Think

A surprising number of candidates believe ORDER BY can only reference plain columns. In fact you can sort by expressions, aliases, column positions, and CASE logic.

Interviewers use this to test whether you understand logical query execution order, because ORDER BY runs after SELECT, which is exactly why aliases work here but not in WHERE.

Why Aliases Are Legal in ORDER BY

Recall the logical execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY. Because SELECT runs before ORDER BY, the column aliases you defined in SELECT already exist when ORDER BY evaluates.

That is the precise reason an alias works in ORDER BY but fails in WHERE, a question interviewers love to connect back to execution order.

SELECT name, salary * 12 AS annual_pay
FROM employees
ORDER BY annual_pay DESC;

Sorting by a Raw Expression

You can also repeat the full expression in ORDER BY instead of using the alias. Both are valid; the result is identical.

Using the alias is cleaner and avoids duplicating logic, but some teams prefer the explicit expression for clarity. Either answer is acceptable in an interview as long as you can justify it.

SELECT name, salary * 12 AS annual_pay
FROM employees
ORDER BY salary * 12 DESC;

Sorting by Column Position (Ordinal)

You may sort by the ordinal position of a select-list item: ORDER BY 2 sorts by the second selected column. It is concise but fragile, because reordering the SELECT list silently changes the sort.

Interview advice: know it exists and that it is generally discouraged in production code for readability and safety.

SELECT name, hire_date
FROM employees
ORDER BY 2 DESC;

Sorting by a Computed CASE

A powerful pattern is sorting by a CASE expression to impose a custom, non-alphabetical order. Here we surface high earners first using a derived priority.

The CASE produces a sort rank that has no column of its own; the engine evaluates it purely for ordering.

SELECT name, salary
FROM employees
ORDER BY CASE WHEN salary >= 100000 THEN 0 ELSE 1 END,
         salary DESC;

Custom Categorical Ordering

Status columns rarely sort sensibly alphabetically. "active, pending, closed" alphabetizes to "active, closed, pending," which is wrong. A CASE maps each value to a deliberate rank.

This is a frequent reporting requirement and a clean way to show you can control ordering beyond the natural sort.

SELECT id, status
FROM orders
ORDER BY CASE status
           WHEN 'active'  THEN 1
           WHEN 'pending' THEN 2
           WHEN 'closed'  THEN 3
           ELSE 4
         END;

Sorting by a Function Result

Any scalar function works in ORDER BY. For example, sort case-insensitively with LOWER, or sort by string length, or by a date part.

Note: wrapping a column in a function during sorting can prevent the engine from using an index on that column, just as it does in WHERE.

SELECT name
FROM employees
ORDER BY LOWER(name) ASC;

Aliases vs Aggregates in GROUP BY Queries

In a grouped query you often sort by an aggregate. You can reference the aggregate's alias in ORDER BY, again because SELECT runs first.

The query below counts orders per customer and sorts by that count descending, using the alias order_count.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

The Alias-Name Collision Gotcha

Edge case interviewers enjoy: what if your alias has the same name as a real column? Most engines resolve a bare name in ORDER BY to the select-list alias first, which can surprise you.

If you alias salary * 2 AS salary and then ORDER BY salary, you sort by the doubled value, not the underlying column. Avoid reusing column names as aliases to sidestep this ambiguity.

SELECT salary * 2 AS salary
FROM employees
ORDER BY salary;

Combining Expressions and Directions

You can mix all of these in one clause: an expression, then an alias, then a plain column, each with its own direction. The example sorts by a computed bonus tier descending, then by name ascending as a tiebreaker.

Layering keys like this is the bread and butter of real reporting queries.

SELECT name, salary,
       salary * 0.1 AS bonus
FROM employees
ORDER BY bonus DESC, name ASC;

Portability Caveats

While expressions and aliases in ORDER BY are widely supported, a few notes for interviews:

Ordinal positions (ORDER BY 2) are standard but discouraged.
Alias visibility in ORDER BY is consistent across major engines, unlike in WHERE/HAVING.
SELECT DISTINCT restricts you: you can only ORDER BY expressions that appear in the select list.

Quick Check

Connect alias visibility to execution order.

Recap

Sorting by expressions and aliases:

ORDER BY accepts aliases, raw expressions, functions, CASE logic, and ordinal positions.
Aliases work because ORDER BY runs after SELECT in logical order.
Use CASE for custom categorical ordering.
Avoid alias names that collide with real columns to prevent ambiguity.
Functions in the sort key can defeat index usage.

Frequently asked questions

Is the “Sorting by Expressions and Aliases” lesson free?

Yes — the full text of “Sorting by Expressions and Aliases” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Sorting by Expressions and Aliases”?

Where alias references are legal in ORDER BY and how to sort by computed logic. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Sorting by Expressions and Aliases” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep