SQL Interview Prep · Lesson

Multi-Column Sorting and NULL Placement

ASC/DESC mixing and how NULLS FIRST/LAST differs across databases.

Multi-Column Sorting and NULL Placement is a free SQL Interview Prep lesson on CoddyKit — lesson 1 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.

Why Sorting Shows Up in Interviews

Interviewers reach for ORDER BY early because it reveals whether you understand that a SQL result set is unordered by default. Without an explicit sort, the database is free to return rows in any order it likes.

A common opener is: "How do you guarantee the rows come back in a specific order?" The only correct answer is an explicit ORDER BY clause. Relying on insertion order, primary-key order, or index order is a classic junior mistake.

In this lesson you will master multi-column sorts and the tricky question of where NULL values land.

ASC and DESC Basics

ORDER BY sorts ascending (ASC) by default. You only need DESC to reverse it. The direction applies per column, not to the whole clause.

Interview tip: state that ASC is the implicit default so you do not have to write it. Many candidates wrongly think one DESC flips every column.

SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC;

Sorting on Multiple Columns

List several columns separated by commas. SQL sorts by the first column, then breaks ties with the second, and so on. Think of it as a tiebreaker chain.

The example sorts by department alphabetically, and within each department by salary highest-first.

Primary sort: department ascending
Tiebreaker: salary descending

SELECT department, name, salary
FROM employees
ORDER BY department ASC, salary DESC;

Mixing ASC and DESC

A frequent interview trap: each column gets its own direction. Writing ORDER BY a, b DESC sorts a ascending and b descending, not both descending.

If you want both descending you must say ORDER BY a DESC, b DESC. Be explicit when the question mixes directions.

SELECT region, sales_year, revenue
FROM sales
ORDER BY region ASC, sales_year DESC;

Where Do NULLs Go?

This is the heart of the lesson. NULL means unknown, so the standard does not force a position, and databases disagree on the default:

PostgreSQL & Oracle: NULLs sort last in ASC, first in DESC.
MySQL & SQL Server: NULLs sort first in ASC, last in DESC (treated as the smallest value).

Interviewers love asking "Where do NULLs appear in this sort?" The honest answer: it depends on the engine unless you control it explicitly.

Controlling NULL Placement Explicitly

To make placement deterministic regardless of engine defaults, use NULLS FIRST or NULLS LAST. PostgreSQL, Oracle, and SQLite support this directly.

The query below forces unknown hire dates to the bottom even though the column is sorted ascending.

SELECT name, hire_date
FROM employees
ORDER BY hire_date ASC NULLS LAST;

Emulating NULLS LAST in MySQL

MySQL does not support the NULLS LAST keyword. The portable workaround is a leading sort key that flags whether the value is NULL.

The expression hire_date IS NULL returns 0 for real dates and 1 for NULLs. Sorting that ascending pushes NULLs to the end, then the second key sorts the actual dates.

SELECT name, hire_date
FROM employees
ORDER BY (hire_date IS NULL) ASC, hire_date ASC;

Emulating NULLS FIRST in SQL Server

SQL Server also lacks NULLS FIRST/LAST. Use a CASE expression as a leading sort key. Here NULLs get rank 0 so they appear first.

This pattern is worth memorizing: a computed flag column placed before the real sort column gives you total control over NULL position in any dialect.

SELECT name, hire_date
FROM employees
ORDER BY CASE WHEN hire_date IS NULL THEN 0 ELSE 1 END,
         hire_date ASC;

Worked Example: Leaderboard

Suppose you build a leaderboard: highest score first, and for tied scores the player who registered earliest ranks higher. Players who never scored (NULL score) belong at the bottom.

The sort needs three keys: a NULL flag, the score descending, and the signup date ascending as the tiebreaker.

SELECT player, score, signup_date
FROM players
ORDER BY (score IS NULL) ASC,
         score DESC,
         signup_date ASC;

Sort Stability Caveat

Another favorite question: "If two rows are equal on every ORDER BY key, what order do they come back in?"

Answer: undefined. SQL sorts are not guaranteed to be stable, so equal rows can appear in any relative order, and that order may change between runs or after an index change.

The fix is to add a fully unique tiebreaker, usually a primary key, so the result is reproducible.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC;

Performance Note on Sorting

Sorting can be expensive. If ORDER BY matches an existing index in the same column order and directions, the engine can read rows pre-sorted and skip a separate sort step.

Mismatched directions (for example index is all ascending but you sort one column descending) usually defeat this, forcing an in-memory or on-disk sort. Mentioning this connection between index order and sort cost signals maturity to an interviewer.

Quick Check

Test your grasp of multi-column direction rules.

Recap

Key takeaways on multi-column sorting and NULL placement:

Result sets are unordered unless you add ORDER BY.
Direction is per column; ASC is the default.
NULL position differs by engine: last in ASC for Postgres/Oracle, first in ASC for MySQL/SQL Server.
Use NULLS FIRST/LAST where supported, or a leading IS NULL / CASE flag elsewhere.
Add a unique tiebreaker (like the primary key) for reproducible order.

Frequently asked questions

Is the “Multi-Column Sorting and NULL Placement” lesson free?

Yes — the full text of “Multi-Column Sorting and NULL Placement” 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 “Multi-Column Sorting and NULL Placement”?

ASC/DESC mixing and how NULLS FIRST/LAST differs across databases. 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 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Multi-Column Sorting and NULL Placement” 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